Tools

[text] dbms all

Viewer

copy download embed printName: dbms all

ASSIGNMENT 1
TABLE 1:
CREATE TABLE STUDENT ( ROLL INT, NAME VARCHAR(30), AGE INT, CITY VARCHAR(30), COURSE VARCHAR(30));
INSERT INTO STUDENT(ROLL, NAME, AGE, CITY,COURSE)
VALUES(3, 'NITIN', 20, 'LKO', 'MSC');
INSERT INTO STUDENT(ROLL, NAME, AGE, CITY,COURSE)
VALUES(6, 'ANKIT', 25, 'LKO', 'MCA');
INSERT INTO STUDENT(ROLL, NAME, AGE, CITY,COURSE)
VALUES(12, 'MANIKA', 23, 'KAN', 'BCA');
INSERT INTO STUDENT(ROLL, NAME, AGE, CITY,COURSE)
VALUES(10, 'ASHISH', 20, 'ALD', 'MSC');
1.) SHOW ALL THE STUDENTS DATA (FROM THE STUDENT TABLE).
--> SELECT * FROM STUDENT;
2.) SHOW ALL THE STUDENTS ROLL NO. AND NAME FROM STUDENT TABLE.
--> SELECT ROLL, NAME FROM STUDENT;
TABLE 2:
CREATE TABLE COURSE ( COURSE VARCHAR(30), C_NAME VARCHAR(50), FEES INT, PAPER INT);
INSERT INTO COURSE(COURSE, C_NAME, FEES, PAPER)
VALUES('MCA', 'Master of Computer Application', 17500, 22);
INSERT INTO COURSE(COURSE, C_NAME, FEES, PAPER)
VALUES('BCA', 'Bachelor of Computer Application', 14000, 25);
INSERT INTO COURSE(COURSE, C_NAME, FEES, PAPER)
VALUES('MSc', 'Master of Science', 15000, 20);
1.) SHOW ALL THE DATA FROM COURSE TABLE.
--> SELECT * FROM COURSE;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSIGNMENT 2
TABLE 1:
CREATE TABLE STUDENT_1 ( ROLL INT, NAME VARCHAR(50), AGE INT, CITY VARCHAR(30), COURSE_ID VARCHAR(30));
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(1, 'ANKIT', 25, 'LKO', 'MCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(12, 'MONIKA', 23, 'KAN', 'BCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(10, 'ASHISH', 20, 'ALD', 'MSc');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(5, 'NITIN', 22, 'LKO', 'MSc');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(3, 'SANDHYA', 24, 'KAN', 'MCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(8, 'ABHISHEK', 22, 'ALD', 'MCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(15, 'SUMAN', 23, 'ALD', 'BCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(4, 'SHILPEE', 25, 'LKO', 'BCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(13, 'ASIF', 21, 'ALD', 'MCA');
INSERT INTO STUDENT_1(ROLL, NAME, AGE, CITY, COURSE_ID)
VALUES(7, 'ABHISHEK', 27, 'KAN', 'MSc');
TABLE 2:
CREATE TABLE COURSE_1 (C_ID VARCHAR(30), C_NAME VARCHAR(30), FEES INT, PAPER INT);
INSERT INTO COURSE_1(C_ID, C_NAME, FEES, PAPER)
VALUES('MCA', 'Master of Computer Application', 17500, 22);
INSERT INTO COURSE_1(C_ID, C_NAME, FEES, PAPER)
VALUES('BCA', 'Bachelor of Computer Application', 14000, 25);
INSERT INTO COURSE_1(C_ID, C_NAME, FEES, PAPER)
VALUES('BSc', 'Bachelor of Science', 10000, 18);
INSERT INTO COURSE_1(C_ID, C_NAME, FEES, PAPER)
VALUES('MSc', 'Master of Science', 15000, 20);
INSERT INTO COURSE_1(C_ID, C_NAME, FEES, PAPER)
VALUES('MBA', 'Master of Business Administration', 8000, 22);
INSERT INTO COURSE_1(C_ID, C_NAME, FEES, PAPER)
VALUES('BBA', 'Bachelor of Business Administration', Null, 22);
1.) SHOW THE NAME AND ROLL OF LIST OF STUDENTS WHO LIVE IN CITY KAN AND ENROLLED IN MSc COURSE.
--> SELECT DISTINCT NAME, ROLL FROM STUDENT_1 WHERE CITY='KAN' AND COURSE='MSc';
2.) SHOW THE ROLL AND CITY OF ALL STUDENT WHO ARE ENROLLED IN MCA, MBA OR MSc COURSE.
--> SELECT DISTINCT ROLL, CITY FROM STUDENT_1 WHERE C_ID='MCA' OR C_ID='MBA' OR C_ID='MSc';
3.) SHOW THE LIST OF ROLL OF ALL THE STUDENTS HAVING AGE 21 AND 25.
--> SELECT DISTINCT ROLL FROM STUDENT_1 WHERE AGE BETWEEN 21 AND 25;
4.) SHOW ROLL AND NAME OF ALL STUDENTS WHOSE NAME START WITH 'S'.
--> SELECT DISTINCT NAME, ROLl FROM STUDENT_1 WHERE NAME LIKE 'S%';
5.) SHOW THE NAME OF THE COURSE WHOSE COURSE FEE IS NULL.
--> SELECT DISTINCT C_NAME FROM COURSE_1 WHERE FEES IS NULL;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSIGNMENT 3:
TABLE 1:
CREATE TABLE COURSE_0 (C_NAME VARCHAR(50), C_NO VARCHAR(30), CREDIT_HR INT, DEPT VARCHAR(50));
INSERT INTO COURSE_0 (C_NAME, C_NO, CREDIT_HR, DEPT)
VALUES('Intro to Computer Science', 'CS1310', 4, 'CS');
INSERT INTO COURSE_0 (C_NAME, C_NO, CREDIT_HR, DEPT)
VALUES('Data Structures', 'CS3320', 4, 'CS');
INSERT INTO COURSE_0 (C_NAME, C_NO, CREDIT_HR, DEPT)
VALUES('Discrete Mathematics', 'MATH2410', 3, 'MATH');
INSERT INTO COURSE_0 (C_NAME, C_NO, CREDIT_HR, DEPT)
VALUES('Database', 'CS3380', 3, 'CS');
TABLE 2:
CREATE TABLE SECTION (S_IDENTIFIER INT, C_NO VARCHAR(30), SEMESTER VARCHAR(15), YEAR INT, INSTRUCTOR VARCHAR(10));
INSERT INTO SECTION (S_IDENTIFIER, C_NO, SEMESTER, YEAR, INSTRUCTOR)
VALUES(85, 'MATH2410', 'FALL', 04, 'King');
INSERT INTO SECTION (S_IDENTIFIER, C_NO, SEMESTER, YEAR, INSTRUCTOR)
VALUES(92, 'CS1310', 'FALL', 04, 'Anderson');
INSERT INTO SECTION (S_IDENTIFIER, C_NO, SEMESTER, YEAR, INSTRUCTOR)
VALUES(102, 'CS3320', 'SPRING', 05, 'Knuth');
INSERT INTO SECTION (S_IDENTIFIER, C_NO, SEMESTER, YEAR, INSTRUCTOR)
VALUES(112, 'MATH2410', 'FALL', 05, 'Chang');
INSERT INTO SECTION (S_IDENTIFIER, C_NO, SEMESTER, YEAR, INSTRUCTOR)
VALUES(119, 'CS1310', 'FALL', 05, 'Anderson');
INSERT INTO SECTION (S_IDENTIFIER, C_NO, SEMESTER, YEAR, INSTRUCTOR)
VALUES(135, 'CS3380', 'FALL', 05, 'Stone');
1.) SHOW ALL COURSE NAME, DEPARTMENT AND INSTRUCTOR NAME.
--> SELECT DISTINCT C_NAME, DEPT, INSTRUCTOR FROM COURSE_0, SECTION WHERE COURSE_0.C_NO=SECTION.C_NO;
2.) SHOW ALL COURSE NAME, SECTION IDENTIFIER WHERE CREDIT HOUR > 3.
--> SELECT DISTINCT C_NAME, S_IDENTIFIER FROM COURSE_0, SECTION WHERE COURSE_0.C_NO=SECTION.C_NO AND CREDIT_HR>3;
3.) SHOW ALL COURSE NAME IN 'SPRING' SEMESTER.
--> SELECT DISTINCT C_NAME FROM COURSE_0, SECTION COURSE_0.C_NO=SECTION.C_NO AND SEMESTER='SPRING';
4.) SHOW ALL COURSE NAME AND DEPT OF INSTRUCTOR 'ANDERSON'.
--> SELECT DISTINCT C_NAME, DEPT FROM COURSE_0, SECTION COURSE_0.C_NO=SECTION.C_NO AND INSTRUCTOR='Anderson';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSIGNMENT 4: DATABASE SCHEMA FOR A CUSTOMER SALE SCENARIO:
1.) CREATE THE TABLE WITH THE APPROPRIATE INTEGRITY CONSTRAINTS.
--> CREATE TABLE CUSTOMER ( CUST_ID INT PRIMARY KEY, CUST_NAME VARCHAR (30));
--> CREATE TABLE ITEM ( ITEM_ID INT PRIMARY KEY, ITEM_NAME VARCHAR(30), ITEM_PRICE INT);
--> CREATE TABLE SALE ( BILL_NO INT PRIMARY KEY, BILL_DATE DATE, CUST_ID INT, ITEM_ID INT, QTY_SOLD INT);
2.) INSERT 5 VALUES (RECORDS) IN EACH TABLE AS YOU ASSUME:
--> INSERT INTO CUSTOMER ( CUST_ID, CUST_NAME)
VALUES ( 01, 'JAVED');
INSERT INTO CUSTOMER ( CUST_ID, CUST_NAME)
VALUES ( 02, 'MAMUN');
INSERT INTO CUSTOMER ( CUST_ID, CUST_NAME)
VALUES ( 03, 'MUSHARRAF');
INSERT INTO CUSTOMER ( CUST_ID, CUST_NAME)
VALUES ( 04, 'MOHIT');
INSERT INTO CUSTOMER ( CUST_ID, CUST_NAME)
VALUES ( 05, 'ABDUL');
--> INSERT INTO ITEM ( ITEM_ID, ITEM_NAME, ITEM_PRICE)
VALUES ( 101, 'LAYS', 20);
INSERT INTO ITEM ( ITEM_ID, ITEM_NAME, ITEM_PRICE)
VALUES ( 102, 'OREO', 15);
INSERT INTO ITEM ( ITEM_ID, ITEM_NAME, ITEM_PRICE)
VALUES ( 103, 'BOURBON', 10);
INSERT INTO ITEM ( ITEM_ID, ITEM_NAME, ITEM_PRICE)
VALUES ( 104, 'TEA', 25);
INSERT INTO ITEM ( ITEM_ID, ITEM_NAME, ITEM_PRICE)
VALUES ( 105, 'CAKE', 300);
--> INSERT INTO SALE ( BILL_NO, BILL_DATE, CUST_ID, ITEM_ID, QTY_SOLD)
VALUES ( 501, '2022-09-21', 01, 105, 50);
INSERT INTO SALE ( BILL_NO, BILL_DATE, CUST_ID, ITEM_ID, QTY_SOLD)
VALUES ( 502, '2022-09-22', 02, 104, 55);
INSERT INTO SALE ( BILL_NO, BILL_DATE, CUST_ID, ITEM_ID, QTY_SOLD)
VALUES ( 503, '2022-09-23', 03, 103, 45);
INSERT INTO SALE ( BILL_NO, BILL_DATE, CUST_ID, ITEM_ID, QTY_SOLD)
VALUES ( 504, '2022-11-16', 04, 102, 40);
INSERT INTO SALE ( BILL_NO, BILL_DATE, CUST_ID, ITEM_ID, QTY_SOLD)
VALUES ( 505, '2022-11-15', 05, 101, 60);
3.) LIST ALL THE BILLS FOR CURRENT DATE WITH CUSTOMER NAME AND ITEM NUMBER.
--> SELECT c.CUST_NAME, i.ITEM_ID, s.BILL_NO FROM CUSTOMER c, ITEM i, SALE s WHERE c.CUST_ID=s.CUST_ID AND s.BILL_DATE=to_char(sysdate);
(Mostly the answer will be none as there is no data entry in above tables of current date, so to avoid this add an entry in the date tables for current date).
4.) LIST THE BILL DETAILS WITH THE QUANTITY SOLD, PRICE OF THE ITEM AND THE FINAL AMOUNT.
--> SELECT i.ITEM_PRICE, s.QTY_SOLD, (i.ITEM_PRICE*s.QTY_SOLD) FINAL_AMOUNT FROM ITEM i, SALE s WHERE i.ITEM_ID=s.ITEM_ID;
5.) LIST THE DETAILS OF THE CUSTOMER WHO HAVE BOUGHT A PRODUCT WHICH HAS A PRICE > 200.
--> SELECT c.CUST_ID, c.CUST_NAME FROM CUSTOMER c, ITEM i, SALE s WHERE ITEM_PRICE>200 AND c.CUST_ID=s.CUST_ID AND i.ITEM_ID=s.ITEM_ID;
6.) GIVE A COUNT OF HOW MANY PRODUCTS HAVE BEEN BOUGHT BY EACH CUSTOMER.
--> SELECT CUST_ID, COUNT(ITEM_ID) FROM SALE GROUP BY CUST_ID;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSIGNMENT 5: DATABASE SCHEMA FOR A EMPLOYEE-PAY SCENARIO.
1.) CREATE THE TABLE WITH THE APPROPRIATE INTEGRITY CONSTRAINTS.
--> CREATE TABLE EMP ( EMP_ID INT, EMP_NAME VARCHAR(50));
CREATE TABLE DEPT ( DEPT_ID INT, DEPT_NAME VARCHAR(50));
CREATE TABLE PAY_DETAILS ( EMP_ID INT, DEPT_ID INT, BASIC INT, ADDITION INT, DEDUCTION INT, DOJ DATE);
CREATE TABLE PAYROLL ( EMP_ID INT, PAY_DATE DATE);
2.) INSERT 5 VALUES (RECORDS) IN EACH TABLE AS YOU ASSUME:
--> INSERT INTO EMP ( EMP_ID, EMP_NAME)
VALUES (1, 'JAVED');
INSERT INTO EMP ( EMP_ID, EMP_NAME)
VALUES (2, 'AMIT');
INSERT INTO EMP ( EMP_ID, EMP_NAME)
VALUES (3, 'AAMIR');
INSERT INTO EMP ( EMP_ID, EMP_NAME)
VALUES (4, 'DINESH');
INSERT INTO EMP ( EMP_ID, EMP_NAME)
VALUES (5, 'MAYANK');
--> INSERT INTO DEPT ( DEPT_ID, DEPT NAME)
VALUES ( 101, 'FINANCE');
INSERT INTO DEPT ( DEPT_ID, DEPT NAME)
VALUES ( 105, 'SALES');
INSERT INTO DEPT ( DEPT_ID, DEPT NAME)
VALUES ( 115, 'MARKETING');
INSERT INTO DEPT ( DEPT_ID, DEPT NAME)
VALUES ( 125, 'ACCOUNTS');
INSERT INTO DEPT ( DEPT_ID, DEPT NAME)
VALUES ( 130, 'OPERATION');
--> INSERT INTO PAY_DETAILS ( EMP_ID, DEPT_ID, BASIC, ADDITION, DEDUCTION, DOJ)
VALUES ( 1, 101, 15000, 5000, 2000, '2021-07-15');
INSERT INTO PAY_DETAILS ( EMP_ID, DEPT_ID, BASIC, ADDITION, DEDUCTION, DOJ)
VALUES ( 2, 105, 12000, 4000, 2000, '2021-08-15');
INSERT INTO PAY_DETAILS ( EMP_ID, DEPT_ID, BASIC, ADDITION, DEDUCTION, DOJ)
VALUES ( 3, 115, 22000, 6000, 3000, '2021-07-10');
INSERT INTO PAY_DETAILS ( EMP_ID, DEPT_ID, BASIC, ADDITION, DEDUCTION, DOJ)
VALUES ( 4, 125, 25000, 5500, 1000, '2021-07-20');
INSERT INTO PAY_DETAILS ( EMP_ID, DEPT_ID, BASIC, ADDITION, DEDUCTION, DOJ)
VALUES ( 5, 130, 30000, 7000, 1500, '2021-08-20');
--> INSERT INTO PAYROLL ( EMP_ID, PAY_DATE)
VALUES ( 1, '2021-08-15');
INSERT INTO PAYROLL ( EMP_ID, PAY_DATE)
VALUES ( 2, '2021-09-15');
INSERT INTO PAYROLL ( EMP_ID, PAY_DATE)
VALUES ( 3, '2021-08-10');
INSERT INTO PAYROLL ( EMP_ID, PAY_DATE)
VALUES ( 4, '2021-08-20');
INSERT INTO PAYROLL ( EMP_ID, PAY_DATE)
VALUES ( 5, '2021-09-20');
3.) LIST THE EMPLOYEE DETAILS DEPARTMENT WISE.
--> SELECT e.EMP_ID, e.EMP_NAME, d.DEPT_NAME FROM EMP e, DEPT d, PAY_DETAILS p WHERE p.EMP_ID=E.EMP_ID AND p.DEPT_ID=D.DEPT_ID ORDER BY DEPT_NAME;
4.) LIST ALL THE EMPLOYEE NAMES WHO JOINED AFTER PARTICULAR DATE.
--> SELECT e.EMP_NAME, p.DOJ FROM EMP e, PAY_DETAILS p WHERE p.EMP_ID=e.EMP_ID AND DOJ>'2021-07-31';
5.) LIST THE DETAILS OF EMPLOYEES WHOSE BASIC SALARY IS BETWEEn 10000 AND 20000.
--> SELECT e.EMP_ID, e.EMP_NAME, d.DEPT_NAME FROM EMP e, DEPT d, PAY_DETAILS p WHERE p.EMP_ID=E.EMP_ID AND p.DEPT_ID=D.DEPT_ID AND BASIC BETWEEN 10000 AND 20000;
6.) GIVE A COUNT OF HOW MANY EMPLOYEES ARE WORKING IN EACH DEPARTMENT.
--> SELECT EMP_ID, COUNT(DEPT_ID) FROM PAY_DETAILS GROUP BY EMP_ID;
7.) GIVE A NAMES OF THE EMPLOYEES WHOSE NET SALARY > 10000.
--> SELECT EMP_NAME FROM EMP WHERE EMP_ID IN ( SELECT EMP_ID FROM PAY_DETAILS WHERE BASIC + ADDITION - DEDUCTION > 10000);
8.) LIST THE DETAILS FOR AN EMPLOYEE ID =5.
--> SELECT e.EMP_ID, e.EMP_NAME, d.DEPT_ID, d.DEPT_NAME, p.BASIC, p.ADDITION. p.DEDUCTION, p.DOJ, a.PAY_DATE FROM EMP e, DEPT d, PAY_DETAILS p, PAYROLL a WHERE
p.EMP_ID=e.EMP_ID AND p.DEPT_ID=d.DEPT_ID AND a.EMP_ID=e.EMP_ID AND e.EMP_ID=5;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSIGNMENT 6: DATABASE SCHEMA FOR A STUDENT LAB SCENARIO.
1.) CREATE THE TABLE WITH THE APPROPRIATE INTEGRITY CONSTRAINTS.
--> CREATE TABLE STUDENT_1 ( STUD_NO INT, STUD_NAME VARCHAR(30), CLASS VARCHAR(10));
CREATE TABLE CLASS_1 ( CLASS VARCHAR(10), DESCRIP VARCHAR(20));
CREATE TABLE LAB_1 ( MACH_NO INT, LAB_NO INT, DESCRIP VARCHAR(20));
CREATE TABLE ALLOTEMNT_1 ( STUD_NO INT, MACH_NO INT, DOWEEK VARCHAR(10));
2.) INSERT 5 VALUES (RECORDS) IN EACH TABLE AS YOU ASSUME:
--> INSERT INTO STUDENT_1 ( STUD_NO, STUD_NAME, CLASS)
VALUES ( 1001, 'JAVED', 'CSE');
INSERT INTO STUDENT_1 ( STUD_NO, STUD_NAME, CLASS)
VALUES ( 1002, 'UTTAM', 'CSIT');
INSERT INTO STUDENT_1 ( STUD_NO, STUD_NAME, CLASS)
VALUES ( 1003, 'AKASH', 'ECE');
INSERT INTO STUDENT_1 ( STUD_NO, STUD_NAME, CLASS)
VALUES ( 1004, 'AMIT', 'ME');
INSERT INTO STUDENT_1 ( STUD_NO, STUD_NAME, CLASS)
VALUES ( 1005, 'AAMIR', 'IT');
--> INSERT INTO CLASS_1 ( CLASS, DESCRIP)
VALUES ( 'CSE', 'THIRD YEAR');
INSERT INTO CLASS_1 ( CLASS, DESCRIP)
VALUES ( 'CSIT', 'THIRD YEAR');
INSERT INTO CLASS_1 ( CLASS, DESCRIP)
VALUES ( 'ECE', 'THIRD YEAR');
INSERT INTO CLASS_1 ( CLASS, DESCRIP)
VALUES ( 'ME', 'THIRD YEAR');
INSERT INTO CLASS_1 ( CLASS, DESCRIP)
VALUES ( 'IT', 'THIRD YEAR');
--> INSERT INTO LAB_1 ( MACH_NO, LAB_NO, DESCRIP)
VALUES ( 7, 2, 'OS LAB');
INSERT INTO LAB_1 ( MACH_NO, LAB_NO, DESCRIP)
VALUES ( 10, 5, 'DBMS LAB');
INSERT INTO LAB_1 ( MACH_NO, LAB_NO, DESCRIP)
VALUES ( 14, 8, 'JAVA LAB');
INSERT INTO LAB_1 ( MACH_NO, LAB_NO, DESCRIP)
VALUES ( 20, 10, 'INSTRUMENTATION LAB');
INSERT INTO LAB_1 ( MACH_NO, LAB_NO, DESCRIP)
VALUES ( 22, 18, 'POWER SYSTEM LAB');
--> INSERT INTO ALLOTMENT_1 ( STUD_NO, MACH_NO, DOWEEK)
VALUES(1001, 7, 'MONDAY');
INSERT INTO ALLOTMENT_1 ( STUD_NO, MACH_NO, DOWEEK)
VALUES(1002, 10, 'TUESDAY');
INSERT INTO ALLOTMENT_1 ( STUD_NO, MACH_NO, DOWEEK)
VALUES(1003, 14, 'WEDNESDAY');
INSERT INTO ALLOTMENT_1 ( STUD_NO, MACH_NO, DOWEEK)
VALUES(1004, 20, 'THURSDAY');
INSERT INTO ALLOTMENT_1 ( STUD_NO, MACH_NO, DOWEEK)
VALUES(1005, 22, 'FRIDAY');
3.) LIST ALL THE MACHINE ALLOTMENTS WITH THE STUDENT NAMES, LAB AND MACHINE NUMBERS.
--> SELECT s.STUD_NAME, l.MACH_NO, l.LAB_NO FROM STUDENT_1 s, LAB_1 l, ALLOTMENT_1 a WHERE a.MACH_NO=l.MACH_NO AND a.STUD_NO=s.STUD_NO;
4.) LIST THE TOTAL NUMBER OF LAB ALLOTMENTS DAY WISE.
--> SELECT l.MACH_NO, l.DESCRIP, a.DOWEEK FROM LAB_1 l, ALLOTEMENT_1 a WHERE a.MACH_NO=l.MACH_NO;
5.) GIVE A COUNT OF HOW MANY MACHINES HAVE BEEN ALLOCATED TO THE 'CSIT' CLASS.
--> SELECT COUNT(MACH_NO) FROM ALLOTEMENT_1 WHERE STUD_NO IN ( SELECT STUD_NO FROM STUDENT_1 WHERE CLASS = 'CSIT';
6.) GIVE A MACHINE ALLOTMENT DEATAILS OF STUD_NO 5 WITH HIS PERSONAL AND CLASS DETAILS.
--> SELECT a.STUD_NO, a.MACH_NO, s.STUD_NAME, s.CLASS FROM ALLOTMENT_1 a, STUDENT_1 s WHERE a.STUD_NO=s.STUD_NO and a.STUD_NO=1005;
7.) COUNT FOR HOW MANY MACHINES HAVE BEEN ALLOCATED IN LAB_NO 1 FOR THE DAY OF THE WEEK AS MONDAY.
--> SELECT COUNT(MACH_NO) FROM ALLOTMENT_1 WHERE STUD_NO IN ( SELECT STUD_NO FROM STUDENT_1 WHERE DOWEEK = 'MONDAY';
8.) HOW MANY STUDENTS CLASS WISE HAVE BEEN ALLOCATED MACHINES IN THE LAB.
--> SELECT COUNT(STUD_NO)"ALLOCATED MACHINES IN THE LAB", CLASS FROM STUDENT_1 WHERE STUD_NO IN (SELECT STUD_NO FROM STUDENT_1) GROUP BY CLASS;
9.) CREATE A VIEW WHICH LISTS OUT THE STUD_NO, STUD_NAME, MACH_NO, LAB_NO, DAYOFWEEK.
--> CREATE VIEW JAVED AS SELECT s.STUD_NAME, s.STUD_NO, a.DOWEEK, l.MACH_NO, l.LAB_NO FROM STUDENT_1 s, LAB_1 l, ALLOTMENT_1 a WHERE a.MACH_NO=l.MACH_NO AND a.STUD_NO=
s.STUD_NO;
--> SELECT * FROM JAVED;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSIGNMENT 7: DATABASE SCHEMA FOR A STUDENT LIBRARY SCENARIO.
1.) CREATE THE TABLE WITH THE APPROPRIATE INTEGRITY CONSTRAINTS.
--> CREATE TABLE STUDENT_2 ( STD_NO INT, STD_NAME VARCHAR(30));
CREATE TABLE MEMBERSHIP ( MEM_NO INT, STD_NO INT);
CREATE TABLE BOOK ( BOOK_NO INT, BOOK_NAME VARCHAR(50), AUTHOR VARCHAR(50));
CREATE TABLE ISS_REC ( ISS_NO INT, ISS_DATE DATE, MEM_NO INT, BOOK_NO INT);
2.) INSERT 5 VALUES (RECORDS) IN EACH TABLE AS YOU ASSUME:
--> INSERT INTO STUDENT_2 (STD_NO, STD_NAME)
VALUES(1001, 'JAVED');
INSERT INTO STUDENT_2 (STD_NO, STD_NAME)
VALUES(1002, 'AMIT');
INSERT INTO STUDENT_2 (STD_NO, STD_NAME)
VALUES(1003, 'AAMIR');
INSERT INTO STUDENT_2 (STD_NO, STD_NAME)
VALUES(1004, 'AKASH');
INSERT INTO STUDENT_2 (STD_NO, STD_NAME)
VALUES(1005, 'ATIF');
--> INSERT INTO MEMBERSHIP ( MEM_NO, STD_NO)
VALUES ( 101, 1001);
INSERT INTO MEMBERSHIP ( MEM_NO, STD_NO)
VALUES ( 102, 1002);
INSERT INTO MEMBERSHIP ( MEM_NO, STD_NO)
VALUES ( 103, 1003);
INSERT INTO MEMBERSHIP ( MEM_NO, STD_NO)
VALUES ( 104, 1004);
INSERT INTO MEMBERSHIP ( MEM_NO, STD_NO)
VALUES ( 105, 1005);
--> INSERT INTO BOOK ( BOOK_NO, BOOK_NAME, AUTHOR)
VALUES ( 1, 'FIVE POINTS SOMEONE', 'CHETAN BHAGAT');
INSERT INTO BOOK ( BOOK_NO, BOOK_NAME, AUTHOR)
VALUES ( 2, 'THE PERFECT US', 'DURJOY DUTTA');
INSERT INTO BOOK ( BOOK_NO, BOOK_NAME, AUTHOR)
VALUES ( 3, 'STORY OF MY LIFE', 'HELEN KELLER');
INSERT INTO BOOK ( BOOK_NO, BOOK_NAME, AUTHOR)
VALUES ( 4, 'HARRY POTTER', 'J.K. ROWLING');
INSERT INTO BOOK ( BOOK_NO, BOOK_NAME, AUTHOR)
VALUES ( 5, 'TELL ME A STORY', 'RAVINDER SINGH');
--> INSERT INTO ISS_REC ( ISS_NO, ISS_DATE, MEM_NO, BOOK_NO)
VALUES ( 201, '2022-11-17', 101, 1);
INSERT INTO ISS_REC ( ISS_NO, ISS_DATE, MEM_NO, BOOK_NO)
VALUES ( 202, '2022-10-25', 102, 2);
INSERT INTO ISS_REC ( ISS_NO, ISS_DATE, MEM_NO, BOOK_NO)
VALUES ( 203, '2022-11-18', 103, 3);
INSERT INTO ISS_REC ( ISS_NO, ISS_DATE, MEM_NO, BOOK_NO)
VALUES ( 204, '2022-07-09', 104, 4);
INSERT INTO ISS_REC ( ISS_NO, ISS_DATE, MEM_NO, BOOK_NO)
VALUES ( 205, '2022-10-30', 105, 5);
3.) LIST ALL THE STUDENT NAMES WITH THE MEMBERSHIP NUMBERS.
--> SELECT s.STD_NAME, m.MEM_NO, FROM STUDENT_2 s, MEMBERSHIP m WHERE m.STD_NO=s.STD_NO;
4.) LIST ALL THE ISSUES FOR THE CURRENT DATE WITH STUDENT AND BOOK NAMES.
--> SELECT i.ISS_NO, s.STD_NAME, b.BOOK_NAME FROM ISS_REC i, MEMBERSHIP m, STUDENT_2 s, BOOK b WHERE i.MEM_NO=m.MEM_NO AND m.STD_NO=s.STD_NO AND i.ISS_DATE=TO_CHAR(SYSDATE);
(Mostly the answer will be none as there is no data entry in above tables of current date, so to avoid this add an entry in the date tables for current date).
5.) LIST THE DETAILS OF STUDENTS WHO BORROWED BOOK WHOSE AUTHOR IS RAVINDER SINGH.
--> SELECT * FROM STUDENT_2 WHERE STD_NO IN ( SELECT STD_NO FROM MEMBERSHIP WHERE MEM_NO IN ( SELECT MEM_NO FROM ISS_REC WHERE BOOK_NO IN ( SELECT BOOK_NO FROM BOOK
WHERE AUTHOR='RAVINDER SINGH')));
6.) GIVE A COUNT OF HOW MANY BOOKS HAVE BEEN BOUGHT BY EACH STUDENT.
--> SELECT s.STD_NO, COUNT(i.BOOK_NO) FROM STUDENT_2 s, MEMBERSHIP m, BOOK b, ISS_REC i WHERE s.STD_NO=m.STD_NO AND b.BOOK_NO=i.BOOK_NO GROUP BY s.STD_NO;
7.) GIVE A LIST OF BOOKS TAKEN BY STUDENT WITH STD_NO AS .
--> SELECT BOOK_NAME FROM BOOK WHERE BOOK_NO IN( SELECT BOOK_NO FROM ISS_REC WHERE MEM_NO IN (SELECT MEM_NO FROM MEMBERSHIP WHERE STD_NO IN (SELECT STD_NO FROM
STUDENT_2 WHERE STD_NO=1005)));
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Editor

You can edit this paste and save as new:

fullscreen copy clear

Syntax Highlighting

Title / Paste Name

Meta robots tag

reCaptcha

File Description

dbms all
Paste Code
07 Dec-2022
19.93 Kb

You can Share it:

Latest Code Pastes

Do you like cookies? 🍪 We use cookies to ensure you get the best experience on our website. Learn more