OBJECTIVE TYPE QUESTIONS /MULTIPLE CHOICE QUESTIONS

1. What is the full form of SQL?

(a) Structured Query Language (b) Structured Query List
(c) Simple Query Language (d) Data Derivation Language

2. What does DML stand for?

(a) Different Mode Level (b) Data Model Language
(c) Data Mode Lane (d) Data Manipulation Language

3. The __________clause of SELECT query allows us to select only those rows in the results that
satisfy a specified condition.
(a) Where (b) from (c) having (d) like

4. Which of the following function is used to FIND the largest value from the given data in
MYSQL?
(a) MAX () (b) MAXIMUM () (c) LARGEST () (c) BIG ()

5. The data types CHAR (n) and VARCHAR (n) are used to create _______ and _______ types of
string/text fields in a database.
(a) Fixed, equal (b) Equal, variable (c) Fixed, variable (d) Variable, equal

6. The term ___________ is use to refer to a record in a table.

(a) Attribute (b) Tuple (c) Row (d) Instance

7. Which command is used for cleaning up the environment (sql with Python)?
(a) my.close (b) is.close (c) con.close (d) mycon.close

8. A relational database consists of a collection of

(a) Tables (b) Fields (c) Records (d) Keys

9. What is the full form of DDL?

(a) Dynamic Data Language (b) Detailed Data Language
(c) Data Definition Language (d) Data Derivation Language

10. A(n) in a table represents a logical relationship among a set of values.

(a) Attribute (b) Key (c) Tuple (d) Entry
11. Name the method which is used for displaying only one resultset.
(a) fetchmany (b) fetchno (c) fetchall (d) fetchone

12. Name the host name used for signing in the database.
(a) localhost (b) localpost (c) localcost (d) none of the above

13. A relational database consists of a collection of

(a)Tuples (b) Attributes (c) Relations (d) Keys

14. Which is the subset of SQL commands used to manipulate database structure including tables?
 (a) Data Definition Language (DDL) (b) Data Manipulation Language (DML)
(c) Both (a) and (b) (d) None

15. The term ____________ is used to refer to a field in a table.

(a) Attribute (b) Tuple (c) Row (d) Instance

16. Consider the following table namely employee:

Employee_id Name Salary
5001 Amit 60000

5009 Sumit 45000

5020 Arpit 70000

Which of the names will not be displayed by the below given query?
SELECT name FROM employee WHERE employee_id>5009;
(a) Amit, Sumit (b) Sumit, Arpit (c) Arpit (d) Amit, Arpit

17. Consider the following query

SELECT name FROM stu WHERE subject LIKE ‘_______ Computer Science’;
Which one of the following has to be added into the blank space to select the subject which
has Computer Science as its ending string?
(a) $ (b) _ (c) || (d) %

18. Consider following SQL statement. What type of statement is this?

SELECT * FROM employee
(a) DML (b) DDL (c) DCL (d) Integrity constraint

19. Which of the following function is not an aggregate function?

(a) Round() (b) Sum() (c) Count () (d) Avg ()

20. Pick the correct username used for logging in database (sql with Python).
(a) root (b) local (c) directory (d) host

21. Aggregate functions can be used in the select list or the _____ clause of a select statement.
They cannot be used in a ______ clause.
(a) Where, having (b) Having, where (c) Group by, having (d) Group by, where

22. Select correct SQL query from below to find the temperature in increasing order of all cites.
(a) SELECT city FROM weather ORDER BY temperature;
(b) SELECT city, temperature FROM weather;
(c) SELECT city, temperature FROM weather ORDER BY temperature;
(d) SELECT city, temperature FROM weather ORDER BY city;
23. In SQL, which command is used to SELECT only one copy of each set of duplicable rows
(a) SELECT DISTINCT (b) SELECT UNIQUE
(c) SELECT DIFFERENT (d) All of the above
24. Which of the following is a SQL aggregate function?
(a) LEFT (b) AVG (c) JOIN (d) LEN

25. The command used for modifying the records is:

(a) update (b) add (c) updateall (d) none of the above

26. An attribute in a relation is foreign key if it is the _________key in any other relation.
(a) Candidate (b) Primary (c) Super (d) Sub

27. Which of the following sublanguages of SQL is used to query information from the data base
and to insert tuples into, delete tuples from, and modify tuples in the database?
(a) DML (Data Manipulation Language)
(b) DDL (Data Definition Language)
(c) Query
(d) Relational Schema

28. Which operator performs pattern matching?

(a) BETWEEN operator (b) LIKE operator
(c) EXISTS operator (d) None of these

29. Which of the following is not a legal method for fetching records from database from within
Python?
(a) fetchone() (b) fetchtwo() (c) fetchall() (d) fetchmany()

30. By default, ORDER BY clause lists the results in _______ order.

(a) Descending (b) Any (c) Same (d) Ascending

31. Which of the following attributes can be considered as a choice for primary key?
(a)Name (b)Street (c) Roll No (d) Subject

32. In the given query which keyword has to be inserted?

INSERT INTO employee______(1002, “Kausar”, 2000);
(a) Table (b) Values (c) Relation (d) Field

33. What SQL statement do we use to display the record of all students whose last name contains 5
letters ending with “A”?
(a) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘_ _ _ _A’;
(b) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘ _ _ _ _ _’;
(c) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘ ????A’;
(d) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘*A’;

34. Consider the table with structure as:

Student (ID, name, dept name, tot_cred)
In the above table, which attribute will form the primary key?
(a)Name (b) Dept (c) total_credits (d) ID
35. Which of the following will you use in the following query to display the unique values of the
column dept_name?
SELECT _____________ dept_name FROM Company;
(a)All (b) From (c) Distinct (d) Name
36. Consider the following query:
SELECT name, instructor name, course______id
FROM instructor;
To display the field heading course with a different heading as id, which keyword must be
used here to rename the field name?
(a)From (b) Rename (c) As (d) Join
37. With SQL, how do you select all the records from a table named “Students” where the value of
the column “FirstName” ends with an “a”?
(a) SELECT * FROM Students WHERE FirstName =’a’
(b) SELECT * FROM Students WHERE FirstName LIKE ‘a%’
(c) SELECT * FROM Students WHERE FirstName LIKE ‘%a’
(d) SELECT * FROM Students WHERE FirstName =’%a%’
38. The HAVING clause does which of the following?
(a) Acts EXACTLY like WHERE clause
(b) Acts like a WHERE clause but is used for columns rather than groups.
(c) Acts like a WHERE clause but is used form groups rather than rows.
(d) Acts like a WHERE clause but is used for rows rather than columns.

39. Which clause is used with “aggregate functions”?

(a) GROUP BY (b) SELECT (c) WHERE (d) Both (a) and (b)

40. To open a connector to Mysql database, which statement is used to connect with mysql?
(a) Connector (b) Connect (c) password (d) username

41. If column “Marks” contains the data set {25, 35, 25, 35, 38}, what will be the output after the
execution of the given query?
SELECT MARKS (DISTINCT) FROM STUDENTS;
(a) 25. 35. 25. 35. 38 (b) 25, 25, 35, 35 (c) 25, 35, 38 (d) 25, 25, 35, 35

42. Which connector is used for linking the database with Python code?
(a) MySQL-connector (b) YesSQL: connector
(c) PostSQL: connector (d) None of the above

43. If column “Salary” contains the data set {1000, 15000, 25000, 10000, 15000}, what will be the
output after the execution of the given query?
SELECT SUM(DISTINCT SALARY) FROM EMPLOYEE;
(a)75000 (b) 25000 (c) 10000 (d) 50000

44. SQL applies conditions on the groups through _____ clause after groups have been formed,
(a) Group by (b) With (c) Where (d) Having

45. To execute all the rows from the result set, which method is used?
(a) fetchall (b) fetchone (c) fetchmany (d) none of the above
46. What is the meaning of “HAVING” clause is SELECT query?
(a) To filter out the summary groups (b) To filter out the column groups
(c) To filter out the row and column values (d) None of the mentioned

47. Which of the following queries contains an error?

(a) Select * from emp where empid = 10003;
(b) Select empid from emp where empid=10006;
(c) Select empid from emp;
(d) Select empid where empid=1009 and lastname=’GUPTA’;

48. Which operator tests column for the absence of data (i.e., NULL value) ?
(a) EXISTS operator (b) NOT operator
(c) IS operator (d) None of these

49. Consider the following query:

SELECT name FROM class WHERE subject_____NULL;
Which comparison operator may be used to fill the blank space in above query?
(a) = (b) LIKE (c) IS/IS Not (d) if

50. Which SQL function is used to count the number of rows in a SQL query?
(a) COUNT () (b) NUMBER () (c) SUM () (d) COUNT (*)

51. With SQL, how can you return the number of not null record in the Project field of “Students”
table?
(a) SELECT COUNT (Project) FROM Students
(b) SELECT COLUMNS (Project) FROM Students
(c) SELECT COLUMNS (*) FROM Students
(d) SELECT COUNT (*) FROM Students

52. Which of the following is not an aggregate function?

(a) Avg (b) Sum (c) With (d) Min

53. All aggregate functions except _______ ignore null values in their input collection.
(a) Count (attribute) (b) Count (*) (c) Avg (d) Sum

54. Which of the following group functions ignore NULL values?

(a) MAX (b) COUNT (c) SUM (d) All of the above

55. What will be the order of the data being sorted after the execution of given query
SELECT * FROM STUDENT ORDER BY ROLL_NO;
(a)Custom Sort (b) Descending (c) Ascending (d) None of the above

56. Where and Having clauses can be used interchangeably in SELECT queries?
(a) True (b) False (c) Only in views (d) With order by
57. A______ is property of the entire relation, which ensures through its value that each tuple is
unique in a relation.
(a) Rows (b) Key (c) Attribute (d) fields

58. The operation whose result contains all pairs of tuples from the two relations, regardless of
whether their attribute values match.
(a) Join (b) Cartesian product (c) Intersection (d) Set difference

59. Consider following SQL statement. What type of statement is this?

CREATE TABLE employee (name VARCHAR, id INTEGER)
(a) DML (b) DDL (c) DCL (d) Integrity constraint

60. The pattern ‘- - - ’ matches any string of ________ three character. ‘- - - %’ matches any string
of ____ three characters.
(a) Atleast, Exactly (b) Exactly, Atleast (c) Atleast, All (d) All, Exactly

VERY SHORT ANSWER QUESTIONS (1 MARKS EACH)

Q1. Name the command/clause which is used to display the records in ascending or descending
order.
Q2. Give example of any two DML commands.
Q3. What is the purpose of SQL?
Q4. What is primary key?
Q5. Which command is used to display a list of already existing tables?
Q6. Which command is used to change the structure of table?
Q7. Which command is used to change the data of the table?
Q8. Which command is used to delete data of the table?
Q9. Which command delete the structure of table?
Q10. Identify the DDL and DML commands from the following:
Create, Delete
Q11. Which clause is used with aggregate functions? (Group by/ Where)
Q12. What do you mean by candidate key?
Q13. Correct the error in the following query.
Select * from RECORD where Rname = %math%;
Q14. What is max () function in SQL?
Q15. What do you mean by degree and cardinality of table?
Q16. Expand DDL and DML
Q17. Which command is used to increase the salary of workers in table salary? (Update / Alter)
Q18. Name the command used to see the structure of table.
Q19. Which aggregate function is used to find sum of column in a table?
Q20. What is the difference between having and where clause?
Q21. Name an aggregate function in SQL which return the average of numeric values.
Q22. What is the use of “like” in SQL?
Q23. Correct the following statement:
Delete table data;
Q24. What do you mean by aggregate function?
Q25. Write two wild card characters which are used with like operator?
Q26. Duplication of record is called ____________
Q27. What is the difference between char and varchar?
=====================*====================*========================

Fill in the blanks

1. SQL stands for _______ Query Language.
2. A connectivity package such as _______ must be imported before writing database
connectivity Python code.
3. The SQL keyword_______ is used to specify the table(s) that contains the data to be
retrieved.
4. To remove duplicate rows from the result of a query, specify the SQL qualifier________ in
select list.
5. To obtain all columns, use a(n) _________ instead of listing all the column names in the
select list.
6. The SQL _______ clause contains the condition that specifies which rows are to the
selected.
7. To sort the rows of the result table, the _________ ________ clause is specified.
8. Columns can be sorted in descending sequence by using the SQL keyword ____________
9. When two conditions must both be true for the rows to be selected, the conditions are
separated by the SQL keyword ________
10. To refer to a set of values needed for a condition, we can use the SQL operation
__________
11. To exclude one or more values (a list of values) using a condition, the SQL keyword
________ ________ should be used.
12. The SQL keyword _______ is used in SQL expressions to select based on patterns
13. The SQL built-in function ________ totals values in numeric columns.
14. The SQL built-in function _________ obtains the largest value in a numeric column.
15. The SQL built-in function _________ obtains the smallest value in a numeric column.
16. The SQL built-in function __________ computes the number of rows in a table.
17. The SELECT clause _________ is used to collect those rows that have the same value in a
specified column.
18. ______________ method returns the result set in the form of tuples containing the records
or rows returned by the sql table.
19. A session between the application program and the database is called______________
20. A __________ query is used to check if data has been added to the table or not.
21. The ______________ function works with data of multiple rows at a time and returns
aggregated value.
22. The _____ clause lets you arrange the result set in the order of single column, multiple
column and custom sort order too.
23. To specify filtering condition for groups, the _____________ clause is used in MYSQL.
24. By default, the ORDER BY clauses sorts the result set in the ________ order.
25. To sort the result set in descending order, ___________ keyword is used with ORDER BY.

True/False Questions
1. The condition in a WHERE clause in a SELECT query can refer to only one value
2. SQL provides the AS keyword, which can be used to assign meaningful column names to
the results of queries using the SQL built-in functions.
3. The rows of the result relation produced by a SELECT statement can be sorted but only by
one column.
4. SQL is a programming language.
5. SELECT DISTINCT is used if a user wishes to see duplicate columns in a query.
6. The HAVING clause acts like a WHERE clause, but it identifies groups that meet a
criterion, rather than rows.
7. The qualifier DISTINCT must be used in an SQL statement when we want to Eliminate
duplicate rows.
8. DISTINCT and its counterpart, ALL, can be used more than once in a SELECT statement.
9. DISTINCT and its counterpart, ALL, can be used together on single field in a SELECT
statement.
10. SUM, AVG, MIN and MAX can only be used with numeric columns.
11. The SQL statement: SELECT salary + Comm AS Total FROM Emp; adds two fields salary
and comm from each row together and lists the results in a column named Total.

12. ORDER BY can be combined with the SELECT statement.

13. Data manipulation language (DML) commands are used to define a database, including
creating, altering, and dropping tables and establishing constraints.
14. The keyword LIKE can be used in a WHERE clause to refer to a range of values.
15. The SQL keyword GROUP BY instructs the DBMS to group together those rows that have
the same value in a column.
16. The keyword BETWEEN can be used in a WHERE clause to refer to a range of values.
17. Read operation on any table means to fetch some useful information from the table.
18. Use fetchall() method to retrieve only one value from a database table.
 19. Row count is a read-only attribute.
20. To disconnect database connection, use connect () method.
21. Update statement is used to insert data into the table.
22. The ORDER BY clause combines all those records that have identical values in a particular
field or a group of fields.
23. The WHERE clause is used to specify filtering conditions for groups.
24. DISTINCT option causes a group function to consider only the unique values of the
argument expression.
25. By default, ORDER BY clause sorts the result set in descending order.
26. COUNT () function ignores duplicate and null values while counting the records.
27. The return value of MAX () function is a numeric value.
28. Multiple row function is also known as scalar function
29. SUM () function is used to count the total number of records in a table.
30. Argument type of AVG () function can be numeric or string data type.

-------ANSWER -------
OBJECTIVE TYPE QUESTIONS /MULTIPLE CHOICE QUESTIONS
1 (a)Structure Query Language 21 (b)Having, where 41 (c)25,35,38

2 (d)Data Manipulation Language 22 (d)SELECT city, temperature 42 (a)MySQL-connector

FROM weather ORDER BY city;

3 (a)Where 23 (a)SELECT DISTINCT 43 (d)50000

4 (a) MAX() 24 (b)AVG 44 (d)Having
5 (c) Fixed, variable 25 (a)update 45 (a)fetchall
6 (b)Tuple 26 (b)Primary 46 (a)To filter out the summary
groups

7 (d)mycon.close 27 (a)DML (Data Manipulation 47 (d)Select empid where

Language) empid=1009 and
lastname=’GUPTA’;

8 (a)Tables 28 (b)LIKE operator 48 (c)IS operator

9 (c)Data Definition Language 29 (b)fetchtwo() 49 (c)IS/IS Not

10 (c)Tuple 30 (d)Ascending 50 (d)COUNT (*)

11 (d)fetchone 31 (c)Roll No 51 (a)SELECT COUNT (Project)
FROM Students
12 (a)localhost 32 (b)Values 52 (c)With
13 (c)Relations 33 (a)SELECT * FROM STUDENTS 53 (b)Count(*)
WHERE LNAME LIKE’-_ _ _ _
A’;
14 (b)Data Manipulation Language 34 (d)ID 54 (d)All of the above
(DML)

15 (a)Attribute 35 (c)Distinct 55 (c)Ascending

16 (a)Amit, Sumit 36 (c)As 56 (b)False
17 (d)% 37 (c)SELECT * FROM Students 57 (b)Key
WHERE FirstName LIKE ‘%a’

18 (a)DML 38 (c)Acts like a WHERE clause but 58 (b)Cartesian product

is used from groups rather than
rows

19 (a)Round() 39 (a)GROUP BY 59 (b)DDL

20 (a)Root 40 (b)Connect 60 (b)Exactly, Atleast

-------ANSWER -------
VERY SHORT ANSWER QUESTIONS (1 MARKS EACH)
ANS Q.N. ANS Q.N. ANS
Q.N.
1 order by clause 2 Insert , Delete 3 SQL is structured query
language. It is a standard
language of all the
RDBMS
4 A field which is 5 show tables; 6 Alter
unique for each and
every record in table is
called primary key.
7 Update 8 Delete 9 Drop

10 Create —DDL and 11 Group by 12 Those fields which can act

Delete —-DML as primary key is called
candidate key.
13 Select * from 14 Ans. It returns the 15 Number of columns in
RECORD where largest value from a table is called degree.
Rname like %math%; particular column. Number of rows in a table
is called cardinality.
 16 Ans. DDL – Data 17 Update 18 Desc
Definition Language,
DML – Data
Manipulation
Language.
19 sum() 20 Having clause can be 21 avg()
used with group by
clause while where
clause can be used
without group by
clause.
22 “Like” operator is used 23 Delete from data 24 A function which perform
to match a particular calculation on multiple
pattern in a particular values and return single
column in SQL. value.
25 % and underscore( _ ) 26 Redundancy 27 Char is fixed length data
type and varchar is
variable length data type.

-------ANSWER -------
Fill in the blanks
1 Structured 11 NOT IN 21 Group/row/ aggregation
function

2 Mysql.connector 12 LIKE 22 ORDER BY

3 FROM 13 SUM 23 Having

4 DISTINCT 14 MAX 24 Ascending

5 Asterisk (*) 15 MIN 25 DESC

6 WHERE 16 COUNT

7 ORDER BY 17 GROUP BY

8 DESC 18 Fetchall()

9 AND 19 Connection
10 IN 20 Select
 -------ANSWER -------
True and False
1 F 11 T 21 F

2 T 12 T 22 F

3 F 13 F 23 F

4 F 14 F 24 T

5 F 15 T 25 F

6 T 16 T 26 T

7 T 17 T 27 T

8 F 18 F 28 F

9 F 19 T 29 F

10 T 20 F 30 F

SHORT ANSWER QUESTIONS (2 MARKS EACH)

Q1. What is the difference between cardinality and degree?.
Q.2 Differentiate between WHERE and HAVING clause.
Q.3 Define Primary Key of a relation in SQL. Give an Example using a dummy table.
Q.4 Consider the following Python code is written to access the record of CODE passed
to function: Complete the missing statements:
def Search(eno):
#Assume basic setup import, connection and cursor is created
query="select * from emp where empno=________".format(eno)
mycursor.execute(query)
results = mycursor._________
print(results)
Q. 5 Differentiate between DDL and DML with one Example each.
Q.6 Answer the following:
i) Name the package for connecting Python with MySQL database.
ii) What is the purpose of cursor object?
Q.7 What do you mean by domain of an attribute in DBMS? Explain with an example.
Q.8 Differentiate between fetchone() and fetchmany() methods with suitable examples.
Q.9 What is Constraint ? Give example of any two constraints.
Q.10 Write the steps to perform an Insert query in database connectivity application.
Table ‘student’ values are rollno, name, age (10,’Ashok’,26)
Q.11 Define Candidate Key and Alternate Key with suitable examples from a table containing
some meaningful data.
Q.12 Define RDBMS. Name any two RDBMS software.
Q.13 What is the purpose of the following clauses in a select statement?
i) ORDER BY ii) HAVING
Q.14 Write SQL queries for the following:
i. Create the table Product with appropriate data types and constraints.
ii. Identify the primary key in Product.
Q.15 Write any two differences between Single_row functions and Aggregate functions.
ANSWERS-(SHORT ANSWER QUESTIONS (2 MARKS
EACH)
ANS .1 Degree - The number of attributes or columns in a relation is called the Degree of the relation.
Cardinality - The number of tuples/ rows in a relation is called the Cardinality of the relation.
ANS.2 WHERE clause is used to select particular rows that satisfy a condition whereas HAVING
clause is used in connection with the aggregate function, GROUP BY clause.
For ex. – select * from student where marks > 75;
This statement shall display the records for all the students who have scored more than 75 marks.
On the contrary, the statement – select * from student group by stream having marks > 75; shall
display the records of all the students grouped together on the basis of stream but only for those
students who have scored marks more than 75.
Ans.3 Primary Key- one or more attribute of a relation used to uniquely identify each and every
tuple in the relation. For Example : In the below Table Student, RollNo can be the Primary Key
RollNo Name Marks
1 Pratham 75
2 Srishti 80
Ans. 4 { } and fetchone()
Ans 5 DDL- Data definition language. Consists of commands used to modify the metadata of a
table. For Example- create table, alter table, drop table
DML-Data manipulation language. Consist of commands used to modify the data of a table.
For Example- insert, delete, update
Ans 6 .i) import mysql.connector
ii) It is the object that helps to execute the SQL queries and facilitate row by row processing of
records in the resultset.
Ans 7 Domain of an attribute is the set of values from which a value may come in a
column. E.g. Domain of section field may be (A,B,C,D).
Ans 8 fetchone() is used to retrieve one record at a time but fetchmany(n) will fetch n
records at a time from the table in the form of a tuple.
Ans 9 .Constraints are the checking condition which we apply on table to ensure the correctness of
data . Example primary key, not null, default, unique etc
Ans 10 import mysql.connector as mydb
conn= mydb.connect(host=”localhost”, user=”root”, passwd=”1234”)
cur=conn.cursor()
cur.execute(“INSERT INTO student values(10,’Ashok’,26);”)
cur.commit()
Ans.11 A table may have more than one such attribute/group of attributes that identifies a tuple
uniquely, all such attribute(s) are known as Candidate Keys. All the candidate key except
primary key are called Alternate key.
 Table: Employee (empno, aadhar_no, voter_id, ename, deptno, sal, city)
In the above table Employee, empno,aadhar_no, voter_id all are candidate key If we define
empno as primary key then remaining candidate keys will be alternate key.
Ans.12 RDBMS stands for Relational Database Management System. It is a program that offers
commands to create, update, and manage the data with multiple tables. Examples of RDBMS are
1. MySQL
2. Oracle
3. Microsoft SQL Server.
Ans.13
i) Order By : This clause is used to arrange the records in ascending or descending order. for
example Select * from book order by price;
ii) Having : HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY
clause. for example Select sum(price) from book group by (subject) having price > 100;
Ans 14.
i) Create table product(Pcode varchar(3) not null Primary key , PName Varchar(20),
UPrice int(4), Manufacture Varchar(20));
ii) Pcode is primary key.
Ans.15
Single row Functions Multiple row functions / Aggregate Functions

It operates on a single row at a time. It operates on multiple rows.

It returns one result per row It returns one result for multiple rows.

It can be used in Select, Where, and

Order It can be used in the select clause only.
by clause.

Max(), Min(), Avg(), Sum(), Count() and

Math, String and Date functions are
Count(*)
examples of single row functions.
are examples of multiple row functions.
 CASE STUDY BASED QUESTIONS/SQL-OUTPUT QUESTIONS (3 MARKS)
Q1. Consider the following tables FACULTY and COURSES and give outputs for SQL queries
(i) to (iii)

FACULTY
F_ID Fname Lname Hire_date Salary
102 Amit Mishra 12-10-1998 12000
103 Nitin Vyas 24-12-1994 8000
104 Rakshit Soni 18-5-2001 14000
105 Rashmi Malhotra 11-9-2004 11000
106 Sulekha Srivastava 5-6-2006 10000
COURSES
C_ID F_ID Cname Fees
C21 102 Grid Computing 40000
C22 106 System Design 16000
C23 104 Computer Security 8000
C24 106 Human Biology 15000
C25 102 Computer Network 20000
C26 105 Visual Basic 6000
i) Select COUNT(DISTINCT F_ID) from COURSES;
ii) Select MIN(Salary) from FACULTY,COURSES where COURSES.F_ID =
FACULTY.F_ID;
iii) Select avg(Salary) from FACULTY where Fname like ‘R%’
Q.2Write output for (i) & (iii) basedon a table COMPANY and CUSTOMER.

COMPANY
CID NAME CITY PRODUCTNAME
111 SONY DELHI TV
222 NOKIA MUMBAI MOBILE
333 ONIDA DELHI TV
444 SONY MUMBAI MOBILE
555 BLACKBERRY MADRAS MOBILE
666 DELL DELHI LAPTOP
CUSTOMER
CUSTID NAME PRICE QTY CID

101 Rohan Sharma 70000 20 222

102 Deepak Kumar 50000 10 666

103 Mohan Kumar 30000 5 111

 104 Sahil Bansal 35000 3 333

105 Neha Soni 25000 7 444

106 Sonal Aggarwal 20000 5 333
107 Arjun Singh 50000 15 666

(i) SELECT COUNT(*) ,CITY FROM COMPANY GROUP BY CITY;

(ii) SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10 ;
(iii) SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;

Q.3 Write output for (i) to (iii) based on the tables ‘Watches’ and ‘Sale’ given below.
Table: Watches
Watchid Watch_Name Price Type Qty_Store
W001 HighTime 10000 Unisex 100
W002 LifeTime 15000 Ladies 150
W003 Wave 20000 Gents 200
W004 HighFashion 7000 Unisex 250
W005 GoldenTime 25000 Gents 100
Table: Sale
Watchid Qty_Sold Quarter
W001 10 1
W003 5 1
W002 20 2
W003 10 2
W001 15 3
W002 20 3
W005 10 3
W003 15 4
i. select quarter, sum(qty_sold) from sale group by quarter;
ii. select watch_name,price,type from watches w, sale s wherew.watchid!=s.watchid;
iii. select watch_name, qty_store, sum(qty_sold), qty_store-sum(qty_sold) “Stock” from
watches
w, sale s where w.watchid=s.watchid group by s.watchid;
Q.4 Write the output for SQL queries (i) to (iii), which are based on the table:
Employees
Employees
Empid Firstname Lastname Designation City Salary
010 Ravi Kumar Manager GZB 75000
105 Harry Waltor Manager GZB 65000
152 Sam Tones Director Paris 80000
 215 Sarah Ackerman Manager Upton 75000
244 Manila Sengupta Clerk New Delhi 50000
300 Robert Samuel Clerk Washington 45000
335 Ritu Tondon Clerk GZB 40000
400 Rachel Lee Salesman New York 32000
441 Peter Thompson Salesman Paris 28000
(i) Select Designation , count(*) from Employees Group by Designation Having
count(*)>=3;
(ii) Select Max (salary), Min(Salary) from Employees Where City in (‘GZB’,
‘Paris’);
(iii) Select Firstname, Lastname from Employees where Firstname like ‘R%’;
Q.5 Write output for queries (i) to (iii), which are based on the table:
Books.
Book_id Book_name Author_name Publisher Price Qty
C0001 Fast Cook Lata Kapoor EPB 355 5
F0001 The Tears William hopkin NIL 650 20
T0001 My First Py Brain& Brooke EPB 350 10
T0002 Brain works A.W. Rossaine TDH 450 15
F0002 Thunderbolts Anna Roberts NIL 750 5
i. Select Count(Publisher) from Books;
ii. Select Max(Price) from books where qty >=15;
iii. Select count(distinct publishers) from books where Price>=400;

ANSWERS
ANS .1 (i) 4 (ii) 6000 (iii) 12500
Ans.2
(i) Count(*) CITY
3 DELHI
2 MUMBAI
1 MADRAS
(ii) MIN (PRICE) -50000
MAX (PRICE) -7000
(iii) AVG (QTY)
11
Ans.3
(i) Quarter sum(qty_sold)
1 15
2 30
3 45
 4 15
(ii) watch_name price type
HighFashion 7000 Unisex
(iii)
watch_name qty_store qty_sold Stock
HighTime 100 25 75
LifeTime 150 40 110
Wave 200 30 170
GoldenTime 100 10 90

Ans4.
(i) Manager 3
Clerk 3
(ii) 80000 28000
(iii) Ravi Kumar
Robert Samuel
Ritu Tondon
Rachel Lee
Ans .5
(i) 3 (ii)650 (iii)TDH

CASE STUDY BASED QUESTIONS (5 MARKS EACH)

1. Write SQL commands for (a) to (e) on the basis of table GRADUATE.

Table: GRADUATE
S.N NAME STIPEN SUBJECT AVERAG DI
O. D E V
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
(a) List the names of those students who have obtained DIV 1 sorted by NAME.
 (b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.
(c) To count the number of students who are either PHYSICS or COMPUTER SC
graduates.
(d) To insert a new row in the GRADUATE table:
11, “KAJOL”, 300, “COMPUTER SC”, 75, 1
(e) Display Name of the students whose average is more than 65.
Q.2 Write SQL commands for (a) to (e) on the basis of table CLUB.
Table: CLUB
COAC COAC AGE SPORTS DATEOFAP PAY SE
H ID H P X
NAME
1. KUKREJA 35 KARATE 27/03/1997 1000 M
2. RAVINA 34 KARATE 20/01/1998 1200 F
3. KARAN 34 SQUASH 19/02/1998 2000 M
4. TARUN 33 BASKETBALL 01/01/1998 1500 M
5. ZUBIN 36 SWIMMING 12/01/1998 750 M
6. KETAKI 36 SWIMMING 24/02/1998 800 F
7. ANKITA 39 SQUASH 20/02/1998 2200 F
8. ZAREEN 37 KARATE 20/02/1998 1100 F
9. KUSH 41 SWIMMING 13/01/1998 900 M
10. SHAILYA 37 BASKETBALL 19/02/1998 1700 M
(a) To show all information about the swimming coaches in the club.
(b) To list names of all coaches with their date of appointment (DATOFAPP) in
descending order.
(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all
the coaches.
(d) To insert in a new row in the CLUB table with the following data:
11, “PRAKASH”, 37, “SQUASH”, {25/02/98}, 2500, “M”
(e ) Display Coachname ,Sports,Pay from the table .

3. Write SQL command for (a) to (e ) on the basis of tables INTERIORS and
NEWONES.
Table: INTERIORS
NO ITEMNAME TYPE DATEOFSTOCK PRICE DISCOUNT
1 Red rose Double bed 23/02/02 32000 15
2 Soft touch Baby cot 20/01/02 9000 10
3 Jerry’s home Baby cot 19/02/02 8500 10
4 Rough wood Office Table 01/01/02 20000 20
5 Comfort zone Double bed 12/01/02 15000 20
6 Jerry look Baby cot 24/02/02 7000 19
7 Lion king Office Table 20/02/02 16000 20
8 Royal tiger Sofa 22/02/02 30000 25
9 Park sitting Sofa 13/12/01 9000 15
 10 Dine Paradise Dining Table 19/02/02 11000 15

Table: NEWONES
NO ITEMNAME TYPE DATEOFSTOCKS PRICE DISCOUNT
11 White wood Double bed 23/03/03 20000 20
12 James 007 Sofa 20/02/03 15000 15
13 Tom look Baby cot 21/02/13 7000 10

(a) To show all information about the sofas from the INTERIORS table.
(b) To list the ITEMNAME which are priced at more than 10,000 from the
INTERIORS table.
( c) To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK
is before
22/01/02 from the INTERIERS table in the descending order of
ITEMNAME.
(d )To display ITEMNAME and DATEOFSTOCK of those items, in which the
discount
Percentage is more than 15 from INTERIORS table.
( e )To count the number of items, whose type is “Double Bed” from INTERIOR table.

4. Write SQL command for (a) to (e) on the bases of tables FURNITURE AND ARRIVALS.
Table: FURNITURE

NO. ITEMNAME TYPE DATEOFSTOCK PRICE DISCOUNT

1 White lotus Double 23/02/02 30000 25
Bed
2 Pink feather Baby cot 20//01/02 7000 20
3 Dolphin Baby cot 19/02/02 9500 20
4 Decent Office 01/01/02 25000 30
Table
5 Comfort zone Double 12/01/02 25000 25
Bed
6 Donald Baby cot 24/02/02 6500 15
7 Royal Finish Office 20/02/02 18000 30
Table
8 Royal tiger Sofa 22/02/02 31000 30
9 Econo sitting Sofa 13/12/01 9500 25
10 Eating Dining 19/02/02 11500 25
paradise Table
Table: ARRIVALS
NO ITEMNAM TYP DATEOFSTOC PRIC DISCOUN
. E E K E T
11 Wood Doubl 23/03/03 25000 25
Comfort e Bed
12 Old Fox Sofa 20/02/03 17000 20
13 Micky Baby 21/02/02 7500 15
cot

(a) To show all information about the baby cots from the FURNITURE table.
(b) To list the ITEMNAME which are priced at more than 15000 from the
FURNITURE table.
(c) To list ITEMNAME AND TYPE of those items, in which DATEOFSTOCK is
before 22/01/02from the FURNITURE table in descending order of
ITEMNAME.
(d) To display ITEMNAME and DATEOFSTOCK of those items, in which
the DISCOUNTpercentage is more than 25 from FURNITURE table.
(e) To insert a new row in the ARRIVALS table with
the following data:14, “Velvet touch”, Double bed”,
{25/03/03}, 25000, 30

5. Write SQL commands for (a) to (e) on the basis of Teacher relation given below:
Relation Teacher
No. Name Ag Department Date of Salary Sex
e join
1. Jugal 34 Computer 10/01/97 12000 M
2. Sharmila 31 History 24/03/98 20000 F
3. Sandeep 32 Maths 12/12/96 30000 M
4. Sangeeta 35 History 01/07/99 40000 F
5. Rakesh 42 Maths 05/09/97 25000 M
6. Shyam 50 History 27/06/98 30000 M
7. Shiv Om 44 Computer 25/02/97 21000 M
8. Shalakha 33 Maths 31/07/97 20000 F

(a) To show all information about the teacher of history department

(b) To list the names of female teacher who are in Hindi department
(c) To list names of all teachers with their date of joining in ascending order.
(d) To display teacher’s Name, Salary, Age for male teacher only
(e) To count the number of teachers with Age>23.
6. Answer the questions (i) and (v) on the basis of the following tables SHOP and
ACCESSORIES.
TABLE SHOP
ID SName Area
S0001 ABC
Computeronics CP
S0002 All Infotech Media GK II
S0003 Tech Shoppe CP
S0004 Greeks Techno Soft
Nehru Place S0005
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
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 Shop 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.
(v) To display all Sname in descending order.
7. Consider the following table GARMENT and FABRIC, Write SQL commands for the
statements (i) to (v)
TABLE GARMENT

GCODE DESCRIPTION PRICE FCODE READYDA

TE
10023 PENCIL SKIRT 1150 F 03 19-DEC-08
10001 FORMAL SHIRT 1250 F 01 12-JAN-08
10012 INFORMAL SHIRT 1550 F 02 06-JUN-08
10024 BABY TOP 750 F 03 07-APR-07
10090 TULIP SKIRT 850 F 02 31-MAR-
07
10019 EVENING GOWN 850 F 03 06-JUN-08
10009 INFORMAL PANT 1500 F 02 20-OCT-08
10007 FORMAL PANT 1350 F 01 09-MAR-
08
10020 FROCK 85 F 04 09-SEP-07
0
10089 SLACKS 750 F 03 20-OCT-08

TABLE FABRIC
FCODE TYPE
F 04 POLYSTER
F 02 COTTON
F 03 SILK
F01 TERELENE
(i) To display GCODE and DESCRIPTION of each GARMENT in descending order of
GCODE.
(ii) To display the details of all the GARMENT, which have READYDATE in between 08-
DEC-07 and16-JUN-08 (inclusive if both the dates).
(iii) To display the average PRICE of all the GARMENT, which are made up of
fabric with FCODE as F03.
(iv) To display fabric wise highest and lowest price of GARMENT from
GARMENT table. (Display FCODE of each GARMENT along with highest and
lowest Price).
(v) To display Gcode whose Price is more than 1000.

ANSWERS:
CASE STUDY BASED QUESTIONS
1.(a) Select Name From GRADUATE Where DIV = 1 Order by Name;
(b) Select Name, stipend, subject, stepend *12 From
GRADUATE
(c) Select count (*) From GRADUATE
Where subject IN (“PHYSICS”, “COMPUTER SC”);
(d) Insert into GRADUATE Values (11, “KAJOL”, 300, “COMPUTER SC”,
75,1);
(e ) Select name from Graduate where average>65

2. (a) Select * From CLUB Where sports = “SWIMMING”;

(b) Select COACHNAME From CLUB order by DATOFAPP desc
(c) Select coachname, pay, age, 0.15 * pay From
CLUB;
(d) Insert into CLUB Values (11, “PRAKASH”, 37, “SQUASH”, {25/02/98],
2500, “M”);
 ( e) Select Coachname ,Sports,Pay from Club .

3 (a) Select * From INTERIORS Where TYPE = “Sofa”;

(b) Select ITEMNAME From INTERIORS Where PRICE >
(c) 10000; Select ITEMNAME, TYPE From INTERIORS
Where DATEOFSTOCK < {22/01/02} Order by ITEMNAME desc;
(d) Select ITEMNAME, DATEOFSTOCK From INTERIORS Where DISCOUNT
> 15;
( e )Select Count (*) From INFERIORS Where TYPE = “Double
Bed”;
4 (a) Select * From FURNITURE Where TYPE = “Baby cot”;
(b) Select ITEMNAME From FURNITURE Where PRICE >
(c) 15000; Select ITEMNAME, TYPE From FURNITURE
Where DATEOFSTOCK < {22/01/02} Order by ITEMNAME desc;
(d) Select ITEMNAME, DATEOFSTOCK From FURNITURE Where
DISCOUNT > 25.
(e) Insert Into ARRIVALS Values (14, “Velvet touch”, “Double bed”,
{25/03/03}, 25000,30);

5
(a) SELECT * FROM Teacher WHERE Department = “History”;
(b) SELECT Name FROM Teacher WHERE Department = “Hindi” and Sex =
“F”;
(c) SELECT Name, Dateofjoin FROM Teacher ORDER BY
Dateofjoin;
d) SELECT Name, Salary, Age FROM Teacher
WHERE Age > 23 AND Sex = ‘M’;
(e ) SELECT COUNT (*) FROM Teacher WHERE Age > 23;

6. (i) SELECT Name, Price FROM ACCESSORIES ORDER BY Price ASC;

(ii) SELECT ID, Price FROM SHOP
WHERE Area = ‘Nehru Place’;
(iii) SELECT MIN (Price)
“Minimum Price”, MAX
(Price) “Maximum
Price”,
Name FROM ACCESSORIES GROUP BY Name;
(iv) SELECT Name, Price, SName
FROM ACCESSORIES A, SHOP S WHERE A. ID = S. ID
(v) Select Sname from Shop order by SName desc;

7. (i) SELECT GCODE, DESCRIPTION FROM GARMENT ORDER BY GCODE DESC;

(ii) SELECT * FROM GARMENT WHERE READY DATE BETWEEN ’08-DEC-
07’ AND ’16-JUN-08’;
(iii) SELECT AVG (PRICE) FROM GARMENT WHERE FCODE = ‘F03’;
(iv) SELECT FCODE, MAX (PRICE), MIN (PRICE) FROM GARMENT
GROUP BY FCODE;
(v) Select Gcode from GARMENT where Price>1000;