SQL Assignment

Q1 Write SQL commands and outputs on the basis of table GRADUATE.

Table: GRADUATE
S.NO. NAME STIPEND SUBJECT AVERAGE DIV
1 KARAN 400 PHYSICS 68 1
2 DIVAKAR 450 COMPUTER SC 68 1
3 DIVYA 300 CHEMISTRY 62 2
4 ARUN 350 PHYSICS 63 1
5 SABINA 500 MATHEMATICS 70 1
6 JOHN 400 CHEMISTRY 55 2
7 ROBERT 250 PHYSICS 64 1
8 RUBINA 450 MATHEMATICS 68 1
9 VIKAS 500 COMPUTER SC 62 1
10. MOHAN 300 MATHEMATICS 57 2

1. List the names of those students who have obtained DIV 1 sorted by NAME.
2. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming
that the STIPEND is paid every month.
3. To count the number of students who are either PHYSICS or COMPUTER SC graduates.
4. To insert a new row in the GRADUATE table:
11, “KAJOL”, 300, “COMPUTER SC”, 75, 1
5. To display the names of all students whose name starts with “A”
6. To change the subject to “PHYSICS” of all students whose stipend is more than 400
7. Increase the stipend of all students by 500
8. To display a report , showing name, subject and bonus (10% of stipend) for all students
9. To list all students sorted by subject in descending order
10. Create an index on column subject
11. Give the output of following SQL statement based on table GRADUATE:
a) Select MIN(AVERAGE) from GRADUATE where SUBJECT= “PHYSICS”;
b) Select SUM(STIPEND) from GRADUATE where DIV=2;
c) Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;
d) Select COUNT(distinct SUBJECT) from GRADUATE;

Q2 In a database,there are two tables given below:

Table: EMPLOYEE
EMPLOYEEID NAME SALES JOBID
E1 SUMIT SINHA 110000 102
E2 VIJAY SINGH 130000 101
TOMAR
E3 AJYA RAJPAL 125000 103
E4 MOHIT RAMANI 140000 102
E5 SHAILJA SINGH 145000 103
Table: JOB
JOBID JOBTITLE SALARY
101 VICE PRESIDENT 200000
102 ADMINISTRATION 125000
ASSISTANT
103 PRESIDENT 80000
104 ADMINISTRATION 70000
105 ACCOUNTANT 65000
a) To display employee ids, names of employees, job ids with corresponding job titles.
b) To display names of employees, sales and corresponding job titles who have achieved sales more than 130000.
c) To display names and corresponding job titles of those employees who have ‘SINGH’(anywhere) in their names.
d) Identify foreign key in the table EMPLOYEE
e) Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table ‘EMPLOYEE’
f) Find out the number of employees having ‘MANAGER’ as job.
g) List the count of employees grouped by JOBID
h) List the sum of employees salaries grouped by JOBID

Pg :1
i) List the maximum salary of employee grouped by their department number
j) List the JOBTITLES name and the number of their employees.

Q3 Answer the questions (a) and (b) on the basis of the following tables STORE and ITEM.
TABLE STORE TABLE ITEM
SNo SName Area INo IName Price SNo
S01 ABC Computronics GK II T01 Mother Board 12000 S01
S02 All Infotech Media CP T02 Hard Disk 5000 S01
S03 Tech Shoppe Nehru Place T03 Keyboard 500 S02
S04 Geeks Techno Soft Nehru Place T04 Mouse 300 S01
S05 Hitech Tech Store CP T05 Mother Board 13000 S02
T06 Keyboard 400 S03
T07 LCD 6000 S04
T08 LCD 5500 S05
T09 Mouse 350 S05
T10 Hard Disk 4500 S03

Write the SQL queries (i) to (iv) and Output of (v)

(i) To display IName and Price of all the items in ascending order of their Price.
(ii) To display SNo and SName of all store location in CP.
(iii) To display Minimum and maximum Price of each IName from the table ITEM.
(iv) To display IName, Price of all items and their respective SName where they are available.
(v) Write the output of the following SQL commands (i) to (v):
a. SELECT DISTINCT IName FROM ITEM WHERE Price >=5000;
b. SELECT Area, COUNT (*) FROM STORE GROUP BY Area;
c. SELECT COUNT (DISTINCT Area) FROM STORE:
d. SELECT IName, Price * 0.05 “ DISCOUNT” FROM ITEM WHERE SNo IN (‘S02’, ‘S03’);
e. SELECT lNAME, SNAME FROM STORE S, ITEM I WHERE S. SNO = I. SNO AND PRICE>1000;

Q4 Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown in (g 1) to (g4) parts
on the basis of tables APPLICANTS and COURSES.
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 Arleena 30000 F A03 2012
2109 Shakti 35000 M A04 2011
1101 Kirat 25000 M A01 2012

TABLE COURSES
C_ID COURSE
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 name 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 (i.e. Course 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.
Pg :1
g) Give the output of following SQL statements:
(g1) SELECT NAME, JOIN YEAR FROM APPLICANTS WHERE GENDER= ‘F’ AND C_ID= ‘02’;
(g2) SELECT MIN (JOINYEAR) FROM APPLICANTS WHERE Gender= ‘M’;
(g3) SELE CT AVG (FEE) FROM APPLICANTS WHERE C_ID= ‘A01’ OR C_ID= ‘A05’;
(g4) SELECT SUM (FEE), C_ID FROM APPLICATIONS GROUP BY C_ID HAVING COUNT (*) =2;

Q5: Consider the tables given below and answer the questions that follow :
Table : Event Table : Celebrity
EventId Event NumPerformers CelebrityID CelebrityID Name Phone FeeCharged
101 Birthday 10 C102 C101 FaizKhan 99101956 200000
C102 SanjayKumar 893466448 250000
102 PromotionParty 20 C103 C103 NeeraKhanKapoor 981166568 300000
103 Engagement 12 C102 C104 ReenaBhatia 65877756 100000
104 Wedding 15 C104
1. Name the Primary keys in both the tables and Foreign key in ‘Event’ table. Can
NumPerformers (Number for performers) be set as the Primary key ? Give reason.
2. How many rows will be present in the Cartesian join of the above mentioned two tables ?
3. To display EventId, Event name, Celebrity Id and Names of celebrities for only those
events that have more than 10 performers.
4. To display Event name, Celebrity Id and Names of celebrities who have ‘‘Khan’’ anywhere
in their names.
5. To display Event name, Names of celebrities and Fee charged for those celebrities who
charge more than 200000.

Theory answers
1. (a) Differentiate between Candidate Key and Primary Key in context of RBDMS.
Ans Candidate Key. A candidate key is the one that is capable of becoming primary key. i.e., a field or attribute
that has unique value for each row in the relation.
Primary Key is a designed attribute or a group of attributes whose values can uniquely identify the tuples in the
relation.
2. Differentiate between Candidate key and Alternate key in context of RDBMS.
Ans Candidate Key. A candidate key is the one that is capable of becoming primary key i.e., a field or attribute that
has unique value for each row in the relation.
A candidate key that is not a primary key is called an Alternate key.
3. Differentiate between primary key and alternate key.
Ans Primary Key. It is the set of one or more attributes that can uniquely identify tuples within a relation.
Alternate Key. It is a candidate key which is not primary key.
4. What are candidate keys in a table? Give a suitable example of candidate keys in a table.
Ans A candidate key is the one that is capable of becoming primary key i., a field or attribute that has unique value
for each row in the relation.
Example Table: ITEM
Ino Item Quantity
101 Pen 560
102 Pencil 340
104 CD 540
10 DVD 200
110 Floppy 400
{ Item: Candidate Key}
5. Differentiate between Data Definition language and Data Manipulation language.
Ans The SQL DDL provides commands for defining relation schemas, deleting relationship, creating indexes and
modifying schemas.
The SQL DML includes a query language to insert, delete and modify tuples in the database.
Data Manipulation Language (DML) is used to put values and manipulate them in tables and other
database objects and Data Definition language (DDL) is used to create tables and other database objects.
Pg :1
 6. What is the different between WHERE and HAVING clause?
Ans The HAVING clause places conditions on groups in contrast to WHERE clause, which places conditions on
individual rows.
7. Write the SQL statement to create EMPLOYEE relation which contains EMPNO, Name, Skill,
PayRate.
8. Create a table with under mentioned structure (Table name is EMP)
EMPNo NUMBER(4)
DeptNo NUMBER(2)
EmpName CHAR(10)
Job CHAR(10)
Manager NUMBER(4)
Hiredate DATE
Salary NUMBER(7,2)
Commission NUMBER(7,2)
--------------------------------------------------------------------------------------
 Tuple: A row in a relation is called a tuple.
 Attribute: A column in a relation is called an attribute. It is also termed as field or data item.
 Degree: Number of attributes in a relation is called degree of a relation.
 Cardinality: Number of tuples in a relation is called cardinality of a relation.
 Primary Key: Primary key is a key that can uniquely identifies the records/tuples in a relation. This key
can never be duplicated and NULL. .
 Alternate Key: All the candidate keys other than the primary keys of a relation are alternate keys for a
relation.
 Select operation : Yields set of set of rows depending upon certain condition. Mathematically it is denoted
as e.g σ rollno >10(student) -means show those rows of student table whose roll no.’s are >10.
 Project Operation : yields set of columns as result which are specified. Mathematically it is denoted as π .
e.g.π rollno,name (student) - means show only rollno and name column only.
 Union Operation : Two relation are said to be union compatible if their degree and column are same. e.g
Relation A Relation B Resultant Relation A U B=
X Y Z X Y Z
X Y Z
X1 Y1 Z1 X1 Y1 Z1
X1 Y1 Z1
X2 Y2 Z2 X2 Y2 Z2
X3 Y3 Z3
X3 Y3 Z3
 Cartesian product: Cartesian Product of two relation A and B gives resultant relation whose no. of column
are sum of degrees of two relation and no. of rows are product of cardinality of two relations.

Relation A Relation B Resultant Relation A X B=

X Y Z U V X Y Z U V
X1 Y1 Z1 U1 V1 X1 Y1 Z1 U V1
X2 Y2 Z2 U2 V2 1
X1 Y1 Z1 U V2
2
X2 Y2 Z2 U V1
The Cartesian product is a binary operation and 1 is denoted by a cross(x). The
Cartesian product of two relations A and B is X2 Y2 Z2 U V2 written as A x B. The Cartesian
product yields a new relation which has (degree 2 number of attributes) equal to the
sum of the degrees of the two relations operated upon. The number of tuples
(cardinality) of the new relation of the product of the number of tuples of the two relations operated upon. The
Cartesian product of two relations yields a relation with all possible combinations of the tuples of the two relations
operated upon.

Pg :1