SARASWATI VIDYA MANDIR,KAMLA NAGAR,AGRA

Computer Science(083)
All questions are compulsory.
Time : 01:00:00 Hrs
Total Marks : 50

1. What are DDL and DML? 2

2. Give the SQL statement to create a table STUDENT with Roll Number, Name, Age and Marks. 2
3. Create a table named PROGRAMMERS with the following structure: 2
P_Name 20 Characters
DOJ Date
SAL NUMBER
4. i) Display the name of the programmer, which has the highest salary. 2
ii) Update the salary of all programmer by 2000 whose name start with letter 'R'.
5. Explain the concept of candidate keys with the help of an appropriate example. 2
6. Explain the concept of cartesian product between two tables, with the help of an appropriate example. 2
7. What is primary and alternate key in a database? Give a suitable example to explain each. 2
8. What do you understand by primary key? Give a suitable example of the primary key from a table
containing some meaningful data. 2
9. Consider the following relations 2
Teach (Name, Address, Course)
Give an expression in the relational algebra for each of the following:
(i) Print all the information about teachers who are teaching the 'DBMS' course.
(ii) Print the names and addresses of those teachers who teach 'computer'.
(iii) List all the teachers who live in Mumbai.
10. Observe the following table carefully and write the names of the most appropriate columns, which can be
considered as (i) candidate keys and (ii) primary key.
2
Table: Product
CID CNAME AMOUNT COUNTRY ITEM
101 ALLE 100000 JMEKA SHOES
111 BEN 20000 FRANCE HELMET
110 RIKI 25000 AMERICA BAG
011 BRETT LEE 105000 AUSTRALIA BAT
11. Answer the questions (a) and (b) on the basis of the following tables SHOPPE and ACCESSORIES. 6
TABLE SHOPPE
Id SName Area
S001 ABC Computeronics CP
S002 All Infotech Media GK II
S003 Tech Shoppe CP
S004 Geeks Tecno Soft Nehru Place
S005 Hitech Tech Store Nehru Place
 TABLE ACCESSORIES
No Name Price Id
A01 Mother Board 12000 S01
A02 Hard Disk 5000 S01
A03 Keyboard 500 S02
A04 Mouse 300 S01
A05 Mother Board 13000 S02
A06 Keyboard 400 S03
A07 LCD 6000 S04
T08 LCD 5500 S05
T09 Mouse 350 S05
T10 Hard Disk 4500 S03
(a) Write the SQL queries:
(i) To display Name and price of all the Accessories in ascending order of their price.
(ii) To display id and Sname of all Shoppe located in Nehru place.
(iii) To display Minimum and Maximum price of each Name of Accessories.
(iv) To display Name,Price of all Accessories and their respective SName,where they are available.
(b) Write the output of the following SQL commands;
(i) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>=5000;
(ii)SELECT AREA.COUNT (*) FROM SHOPPE GROUP BY AREA;
(iii)SELECT COUNT (DISTINCT AREA) FROM SHOPPE;
(iv)SELECT NAME,PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERESNO IN ('S02','S03');
12. Write SQL queries for (a) to (f) and write the output for the SQL queries mentioned in (g) parts (i) to (iv) on
the basis of tables ITEMS and TRADERS.
6
TABLE: ITEMS
code IName Qty price company Tcode
1001 DIGITAL PAD 121 120 11000 XENITA T01
1006 LED SCREEN 40 70 38000 SANTORA T02
1004 CAR GPS SYSTEM 50 2150 GEOKNOW T01
1003 DIGITAL CAMERA 12X 160 8000 DIGICLICK T02
1005 PEN DRIVE 32 GB 600 1200 STOREHOME T03
TABLE: TRADERS
Tcode TName City
T01 ELECTRONIC SALES MUMBAI
T03 BUSY STORE CORP DELHI
T02 DISP HOUSE INC CHENNAI
(a) To display the details of all the items in ascending order of item names(i.e. INAME).
(b) To display item name and price of all those items whose price is in the range of 10000 nd 22000(both
values inclusive)
(c) To display the number of items, which are traded by each trader. The expected output of this query
should be:
T01 2

T03 1

T02 2
 (d) To display the Price, item name(ie.name) and quantity(ie.Qty) of those items, which have quantity more
than 150.
(e) To display the names of those traders, who are either from DELHI or from MUMBAI.
(f) To display the name of the companies and the name of the items in descending order of company
names.
(g) Obtain the outputs of the following SQL queries based on the data given in the tables ITEMS and
TRADERS above.
(i) SELECT MAX(Price), MIN(Price) FROM ITEMS;
(ii) SELECT Price * Qty AMOUNT
FROM ITEMS WHERE Code=1004;
(iii) SELECT DISTINCT Tcode FROM ITEMS;
(iv) SELECT IName, TName
FROM ITEMS I, TRADERS T
WHERE I.Code=T.TCode AND Qty<100;

13. Write SQL queries for (a) to (f) and write the outputs for (g) parts (i) to (iv) on the basis of tables
APPLICANTS and COURSES. 6
TABLE: APPLICANTS
No NAME FEE GENDER C_ID JOINYEAR
1012 Amandeep 30000 M A01 2012
1102 Avisha 25000 F A02 2009
1103 Ekant 30000 M A02 2011
1049 Arun 30000 M A03 2009
1025 Amber 40000 M A02 2011
1106 Ela 40000 F A05 2010
1017 Nikita 35000 F A03 2012
1108 Arluna 30000 F A03 2012
2109 Shakti 35000 M A04 2011
1101 Kirat 25000 M A01 2012
TABLE: COURSES
C_ID COURSES
A01 FASHION DESIGN
A02 NETWORKING
A03 HOTEL MANAGEMENT
A04 EVENT MANAGEMENT
A05 OFFICE MANAGEMENT
(a) To display NAME, FEE, Gender, JOINYEAR about the APPLICANTS, who have joined
before 2010.
(b) To display the names of applicants, who are paying FEE more than 30000.
(c) To display the names of all applicants in ascending order of their joinyear.
(d) To display the year and the total number of applicants joined in each year
from the table APPLICANTS>
(e) To display the C_ID and the number of applicants registered in the course
from the APPLICANTS table.
(f) To display the applicant's name with their respective course's name from the
tables APPLICANTS and COURSES.
(g) Give the output of the following SQL statements:
(i) SELECT NAME,JOINYEAR FROM APPLICANTS WHERE GENDER='F' AND C_ID='A02';
(ii) SELECT MIN(JOINYEAR) FROM APPLICANTS WHERE GENDER='M';
 (iii) SELECT AVG(FEE) FROM APPLICANTS WHERE C_ID='A01' OR C_ID='A05';
(iv) SELECT SUM(FEE), C_ID FROM APPLICANTS GROUP BY C_ID HAVING COUNT(*)=2;
14. Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of table STUDENT
TABLE: STUDENT
6
SNO NAME STREAM FEES AGE SEX
1 ARUN KUMAR COMPUTER 750.00 17 M
2 DIVYA JENEJA COMPUTER 750.00 18 F
3 KESHAR MEHRA BIOLOGY 500.00 16 M
4 HARISH SINGH ENG.DR 350.00 18 M
5 PRACHI ECONOMICS 300.00 19 F
6 NISHA ARORA COMPUTER 750.00 15 F
7 DEEPAK KUMAR ECONOMICS 300.00 16 M
8 SARIKA VASWANI BIOLOGY 500.00 15 F
(a) List the name of all students, who have taken stream as COMPUTER.
(b) To count the number of female students.
(c) To display the number of students stream wise.
(d) To insert a new row in the STUDENT table
9,'KARISHMA','ECONOMICS',300.18,'F'
(e) To display a report, listing NAME, STREAM,SEX and stipend, where stipend is 20% of fees.
(f) To display all the records in sorted order of name.
(g) Give the output of the following SQL statements based on STUDENT table:
(i) SELECT AVG(FEES) FROM STUDENT WHERE STREAM='COMPUTER';
(ii) SELECT MAX(AGE) FROM STUDENT;
(iii) SELECT COUNT(DISTINCT STREAM) FROM STUDENT;
(iv) SELECT SUM(FEES) FROM STUDENT GROUP BY STREAM;
15. Write SQL commands for (a) to (d) and write the outputs for (e) and (f) on the basis of table EMPLOYEE
TABLE: EMPLOYEE 6
SNO NAME BASIC DEPARTMENT DATEOFAPP AGE SEX
1 KARAN 8000 PERSONEL 27/03/97 35 M
2 DIVAKAR 9500 COMPUTER 20/01/98 34 M
3 DIVYA 7300 ACCOUNTS 19/02/97 34 F
4 ARUN 8350 PERSONNEL 01/01/95 33 M
5 SABINA 9500 ACCOUNTS 12/01/96 36 F
6 JOHN 7400 FINANCE 24/02/97 36 M
7 ROBERT 8250 PERSONNEL 20/02/97 39 M
8 RUBINA 9450 MAINTENANCE 22/02/98 37 F
9 VIKAS 7500 COMPUTER 13/01/94 41 M
10 MOHAN 9300 MAINTENANCE 19/02/98 37 M
(a) List the names of the employees, who are more than 34 years old sorted by NAME.
(b) Display a report, listing NAME, BASIC, DEPARTMENT AND annual salary. Annual salary equals to
BASIC*12.
(c) To count the number of employees, who are either working in PERSONNEL or COMPUTER
department.
(d) To insert a new row in the EMPLOYEE table
11,'VIJAY',9300,'FINANCE','13/7/98',35,"M"
(e) Give the output of the following SQL statements based on table EMPLOYEE:
(i) SELECT SUM(BASIC) FROM EMPLOYEE WHERE DEPARTMENT='PERSONNEL';
(ii) SELECT AVG(BASIC) FROM EMPLOYEE WHERE SEX='F';
(iii) SELECT MAX(BASIC) FROM EMPLOYEE WHERE DATEOFAPP>'22/02/97';
(iv) SELECT COUNT (DISTINCT DEPARTMENT) FROM EMPLOYEE;
(f) Assume that there is one more table INCHARGE in the database as shown below:
TABLE: INCHARGE
DEPT HEAD
PERSONNEL RAHUL
COMPUTER SATYAM
ACCOUNTS NATH
FINANCE GANESH
MAINTENANCE JACOB
What will be the output of the following query:
SELECT NAME, HEAD
FROM EMPLOYEE E.INCHARGE.I
WHERE E.DEPARTMENT=I.DEPT;