fee
Structured
Query Language
In this Chapter...
# SQLStatements
# SQL Data Types
+ DDL Statements/Commands
# DML Statements/Commands
‘+ Operatorin SQL
Structured Query Language (SQL) is the most popular query
Tanguage used by major relational database management
systems such as MySQL, ORACLE, SQL Server, ee. SQL is
easy to learn asthe statements comprise of descriptive
english words and are not ease sensitive.
SQL. provides statements for defining the structure ofthe
data, manipulating data inthe database, declaring constraints
and retrieving data from the database in various ways,
depending on your requirements,
SQL. provides variety of tasks such as
* Querying data
* Creating, replacing altering and dropping tables.
+ Inserting, updating and deleting rows ina table
* Controlling access to the database
* Cuaranteving database consistency and integrity
Advantages of SQL
() SQLis portable It isnot platform dependent, it ean be
used in all types of devices; PCs, laptops and even
mobile phones aso,
(i) High speed SQL. queries ean be used to retrieve large
amount of records from a database quickly and
efficiently
mn and understand,
‘© Working with NULL Values
‘= Aggregate Functions
‘= GROUP BY Statement
* HAVING Clause
Join
(iv) SQL is used for relational database SQL is widely
used for relational database.
(9) SQL acts as both programming language and
interactive language SQL. can do both the jobs of being,
& programming language as well as an interactive
language at the san
(04) Clientserver language SQI. provides el
time
nt-server
architecture. Its used for linking front end comp
and back end databases
pports object hased programming With the
emergence of object based programming object storage
capabilities ae extended to relation
Disadvantages of SQL
(0) Difficulty in interfacing Interfacing a SQL database is
‘more complex than adding a few lines of eode.
(i) More features implemented in proprietary way
Although SQL databases confirm to ANSI and ISO
standards, some databases go for proprictary extensions
to standard SQL to ensure vendor locksin
SQL Statements
SQL command or statement isa special kindof sentence that
contains clauses and all end with a semicolon() just asa
sentence ends with a period,
‘There are four types of SQL statements
‘CBSE_TG) ‘CBSE Term | Computer Science Xil
DDL (Data Definition Language)
Itprovides statements for creation and deletion of the database
tables, views, ete. The DDL provides a set of definitions to
specify the storage struct
Some DDL state
(i) CREATE used to create new table in the database
(i) DROP used to delete tables from the database,
(ii) ALTER used to change the structure ofthe database
table. This statement can add up additional column,
drop existing, and even change the datatype of
columns involved in a database table,
(iv) RENAME used to rename a table,
DML (Data Manipulation Language)
It provides statements for manipulating the database objects,
It is used to query the databases for information retri
n a database system,
ts are as follows
Some DML. statements are as follows
i) INSERT used to insert data into a table
(ii) SELECT used to retrieve data from a database.
(i) UPDATE used to update existing data within a table,
(iv) DELETE used to delete all records from a table.
DCL (Data Control Language)
Itis used to assign security levels in database, which involves
multiple user setups. They are used to grant defined role and
access privileges to the users
Some DOL state
(i) CRANT used to give user’s access privileges to database
(i) REVOKE used to withdraw access privileaes given
with grant command.
TCL (Transaction Control Language)
Itis used for controlling the transactions in a database
system, These are also used to manage the changes made by
DML.
Some TCL statements ae as follows
(0) COMMIT used to save the work don
(i) SAVEPOINT used to identify point in a transaction to
which you eat later rollback.
(i) ROLLBACK sed to restore database to original since
the list COMMIT,
(is) SEP TRANSACTION establishes prope
current transactions
In this chapter, we will diseuss only DDL and DML.
statements
nts are as follows
Rules for SQL commands
Rules for SQL commands are given belov
(i) SQL. statements ean be typed in lowercase or uppercase
letter. SQL statements are not exse sensitive,
(il) The statements can be typed in single line or multiple
lines.
{ii) A semicolon () is used to terminate the SQL
statements.
(iv) The statements may be distributed across the line but
keywords cannot be
(9) A.comma () is used to separate parameters without a
clause,
(04) Characters and date constants or literals must be
enclosed in single quotes (A)
(ii) A command ean be typed either fll or First four
characters
SQL Data Types
Data types are declared to identify the type of data that will
be stored in a particular field or variable,
‘The following list of general SQL data types are given below
Data Type Syntax Explanation (if applicable)
INTEGER INTEGER AS2-itsigned integer value and
or INT its range from -2LTAS3BE8 to
BL TASIOA7
SMALLINT SHALLINT A 16-bit signed integer val
it range from “32768 to 32767
NUMERIC —WIMERIC Where, psa precision value and
@.5) sie sede vale. ez namerie
(6.2) 46 digit number that has 4
digitbefore the decimal and 2
dlisitafter the decimal
DECIMAL DECIMAL Where, pisaprocision vale ands
@.s) Isasealevalue(oan
NUMERI
REAL, REAL Single-precision floating point
number,
DOUBLE DOUBLE Double-precision lating point
PRECISION RECISION _nunber.
FLOAT FLONT(p) Where, pis a precision value
ARACTER CHAR(>Q —— Where,xisthenumberof
‘haraters tobe stor. This data
typewillaccupy space for NULL.
values. Kean old atmos 255
characters
CHARACTER VARCHAROX) Where, xis the number of
VARYING characters tobe stored. Tewill
fceupy space for NULL values. It
‘can hol tmost 2000 characters
CHARACTER vaRcHARe
VARYING ()
hold 4000 bytes of characters and
tse only Ore.
bare,
TIME,
OATE
TIME
Stores year, month and day values
Stores hour, minute and
second vals,
)/CBSE_TG) SQL Command Basics
SQL database isa way of organizing a group of tables and
table stores the data inthe form of rows and columns.
To create a bunch of different tables that share a common
theme, you would zroup them into one database to make the
‘management process easier. So, for manipulating data, we
need to know about database commands, which are
described below
Creating and Using a Database
Create adatabase Creating database isan easier tsk. You
need to just type the name of the database in a CREATE
DATABASE command.
Syntax
CREATE OATABASE CIF NOT
EXISTS Jedatabase_ name>
Here, CREATE DATABASE command will eeate an empty
database with the specified name and would not contain any
table
IF NOT EXISTS is an optional part ofthis statement which
prevents you from an err there exists a database with the
siven name in the database catalog.
For example, nysql>CRERTE ATABASE 800K
Output Query 0k, 1 row affected «0.01 sec»
Select a Database
Creating database is not enough for use. Before working with
tables, ist you have to seeet the database. The only thing,
need to he considered before selecting a database is that it
iis alread exit To seeet a database USE command is used
Syntax USE
For example, mysql>USE ENSRCOK
\where, USE command makes the specified database asa
current working database and EGNBOOK is the database
Output database charged
‘Show Databases
To check the names ofthe existing databases on the server you
need to use the SHOW command. This will provide you the
information about databases and the contents availble in it
Symtax SHOW DATABASES;
For example, ysqI>SH0 DATABASES:
Output
T batabace
aot
scvo0.
covtes
{STUDENT
CBSE Term Il | Computer Science Xil
Dropping a Database
Database ean he removed or deleted using DROP command,
But before deleting a database make sure that you do not
need the data stored in different tables of a database because
‘when you delete a database, al its tables also gets removed
along with it
Symtax DROP OATABASE :
For example,
rnysql@0ROP DATABASE. SCHOOL
Output
Query Ok, 1 row affected (0.04 sec)
DDL Statements/Commands
Some DDI stat
CREATE Statement
‘The CREATE statement is used to ereate a table in a
database. In this command, we need to give information
ahout table like number of columns, rows and its types and
‘constraints
Syntax
CREATE TABLE
c
(()]
{constraints}
-{()]
Constraints]
)]
constraints]
sare as flows
)
The data type specifies what type of data, the column can
hold and the size or constraint is optional
‘eg If we want to ereate a table PERSONS that contains five
‘columns: P_Id, FirstName, LastName, Address and City.
We use the following CREATE statement
CREATE TABLE PERSONS
Pid INT Primary Key,
FirstName VARCHAR(25)NOT NULL
LastName VARCHAR(25)
[Address VARCHAR(3D)
City VARCHAR( 25)
d
Constraints
Constraints are the conditions thatthe table must satis
‘These can be enforced on the attributes ofa relation. These
‘ean be specified atthe time of ereating table, They are used to
‘ensure integrity ofa relation, hence named as integrity
constraints
)/CBSE_TG) ‘CBSE Term | Computer Science Xil
Some types of constraints a
* NOT NULL Constraint It ensures that a column cannot
store NULL value,
= UNIQUE Constraint Its used to uniquely identify exch
record in a database,
PRIMARY KEY Constraint It ensures that a column
have an unique identity, which helps to find a particular
record ina table and no column that is part ofthe primary
kkey constraint ean contain a NULL value,
* FOREIGN KEY Constraint It designates a column oF
combination of columns as foreign key and establishes
its relationship with a primary key in different tables
= CHECK KEY Constraint Its used to define condition,
which column in each row must satisfy
= DEFAULT Constraint It inserts defi valu
column,
Show Statement
The show or list table is very important when we have many
databases that contain various tables. Sometimes the table
names are the sume in many databases, In that case, this
query is very useful, We ean get the numberof table
information of a database using the following statement.
aysal>
eg.
Suppose we have database Company in which Department,
Accountant, Wages are tables. To show the name ofall the
tables present in database Company, so following command
isused
sysqi-Use Company: Database changed
sysq>SHOM TABLES;
Output
es in_Conpany
Department :
Accountant H
Wages
DESCRIBE Statement
DESCRIBE or DESC command is used to verify the
structure of table that you have ereated.
‘This command display the column names, available data
items with their datatypes,
Symtax DESCRIBE :
DESC
For example, DESC PERSONS,
dint
varchar(25)
varcnar(25}
varchar (20)!
varonar(28)! YES
DROP Statement
‘The DROP statement is used to remove the table definition
and all data, constraints and permission specified for that
table,
You have to be careful while using the DROP command,
‘eeause once the table is deleted, then all the information
available in the table would be lost forever. But there is a
‘condition for dropping a table; it must be empty. A table with
rows init eannot be dropped.
Symtax DROP TABLE tabe_nane
eg. DROP TABLE EMPLOYEE:
‘The above query will delete the table EMPLOYEE and after
this, notable with Employee name would exist.
We cannot even rollback after dropping a table.
ALTER Statement
‘The ALTER statement is used to add, delete or modify
‘columns and constraints in the existing table.
To ADD a Column
Syntax
ALTER TABLE table_name ADO column name
data_type:
‘eg ALTER TABLE STUDENT ADD Section CHAR:
‘The above query will add column Section to STUDENT
table, whose data type is character.
To DROP Column
Syntax ALTER TABLE table_nane ORDP COLUMN colunn_ nan:
‘eg ALTER TABLE STUDENT DROP COLUMN location
‘The above query will delete a column loca
STUDENT table
To MODIFY Column Data Type
Syntax ALTER TABLE table_nane MODIFY colunn_name
data_type
‘eg. ALTER TABLE STUDENT MDIFY Feo NMER(I5.2)
‘The above query will modify the data type of Fee eo
STUDENT table.
1 from
umn, of|
)/CBSE_TG) To DELETE a Constraint
Syntax
ALTER TABLE table_name DROP
Constraint_Name:
eg, ALTER TABLE STUDENT OROP Primary Key:
The above query will delete the primary key constraint from
STUDENT table,
RENAME Statement
‘The RENAME statement is used to rer
Syntax
RENAME old_table_name TO new table_nane:
ce. RENENE STUDENT TO CANDIOATE
‘The above query will rename STUDENT table to
CANDIDATE,
DML Statements/Commands
Some DML statements areas fllows
INSERT Statement
‘The INSERT statement is used to insert a new row/data in a
table,
Syntax
INSERT INTO table pane VALUES
(vauel,
me a table,
value2, valued...)
INSERT INTO table_nane(columnl, colun2.
colunnd,... VALUES(valuel, value2, value...)
eg Ifwe have the following PERSONS table
Address City
LastName FirstName
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borsyn 23 Sandnes
3 Pettersen Kari Storgt20.—_Stavanger
and we want to insert a new row in the PERSONS table,
We use the following SQL statement
INSERT INTO PERSONS VALUES (4, “Ni Tsen
‘Bakken 2°, "Stavanger" )
‘The PERSONS table will now look like this
Shan
Pld LastName FirstName Address City
1 Hansen Ol, ‘Timoteivn 10 Sandnes
2 Svendson Tove Borgyn253—_Sandnes
3 Pettersen Kart Stongt 20
4 Nilsen Johan Bakken 2 Stavanger
CBSE Term Il | Computer Science Xil
Insert Data Only in Specified Columns
Its also possible to add data only in specific cal
‘eg. The following SQL statement will adda new row, but
add data only in the P_Ad, LastName and the FirstName
We use the following SQL statement
INSERT INTO PERSONS(P_Id, Lastiane
Firstlane)
VALUES(S. “Tjessen, “Jakob')
‘The PERSONS table will now look like this
PLId_ LastName FirstName Address City
1 Hansen Ol ‘Timotelm10 _ Sandnes
2 Svemdson Tove Borgo 23 Sandnes
3 Pettersen Kart Storgt20 Stavanger
4 Nien Johan Bakken 2 Stavanger
5 Tessem Jakob NULL NULL
SELECT Statement
‘The SELECT statement is used to select data from a
database or view table information, The result is stored in a
result table, called the result set
Syntax
‘To select some specify colums
SELECT coluen_nane(s) FROM table rane:
‘To select all columns
SELECT * FROM table_nane:
SQLis not ease sensitive. SELECT is the same as select. The
asterisk (*) is a quick way of selecting all columns.
In SQL, SELECT clause is used to list the attributes desired
in the result ofa query and FROM clause is used to list the
relations from which such eohumns are to be extracted,
eg Ifwe want to select the content ofthe columns named
LastName and FirstName from the PERSONS table.
We have to use the following SEL
SELECT LastName, FirstName FROM PERSONS:
‘The result set will look like this
T statement
LastName FirstName
Hansen ol.
Svendon Tove
Pettersen Kart
‘eg Ifwe want to select all the columns from the PERSONS,
table,
ELECT statement
)/CBSE_TG)
We have to use the fllowi
SELECT *
10M PERSONS: ‘CBSE Term | Computer Science Xil
‘The result set will look like this
PAd LastName FirstName Address City
1 Hansen Oly Timotelmn 10 Sundaes
2 Swendson Tove Borgyn 23. Sandnes
3 Pettersen Kari Storgt 20). Stavanger
Column Alias
Column alias is used to temporarily rename a table's column
for the purpose of a particular query. This renaming is a
temporary change and the actual column name does not
change in the database.
Symtax SELECT AS FROM
table_pane>
Alias_name specifies the reference name ofthe specified
column.
For example, to elect the DO} of all the employees
referenced as Joining_Date from the table COMPANY the
query would be like
nysql> SELECT DOY AS Joiming_Oate FROM COMPANY
(Query produces the following output
TO Toining pate.
pai2-02
2002-12
004-08
2007-09
2001-07
$eat2-05
1 eao1-o2
T rows in set (0.00
Operators in SQL
SQL. supports diferent types of operators, some ofthem are
described below
Arithmetic Operators
‘These operators are used to perform mathematical
calculations, such as addition, subtraction, multiplication,
division and remainder,
Some most important arithmetic aperators used in SQL are
OPERATOR
+ (Addition)
~ (Subtraction)
DESCRIPTION
Add the two arguments together
Subtact the second argument from the st
argument
* [Mulplication) Multiples the two arguments
(Division) Divide the fist argument by the second
argument.
Divide the fist argument from the second
argument and proses the remainder ofthat
‘operation
*% (Modulo)
Syntax
SELECT
FROM ]
WERE 1
For example, query to display EMP_NAME,
EMP_DEPT_NO and 20% of EMP_SALARY for each
employee for Socal fund.
riysql>SELECT EMP_NEME, EP_DEPT_NO,
ENP _SALARY#0.20,
FROM cowPan
Above query produces the following output
Vikas witta
Puneet dain
| sachin vats
{ uaay singh
Ravi Shukla
| Vinay Rana’
ros n set (0.00 sec)
Arithmetic operators can be implemented through simple
SELECT statement without any table, This aets like «
Fanetion,
For example,
SELECT 354205;
Above query produces the following ont
T row in set
0.03 see
Comparison Operators
“These operators are used to test or compare the value of to
operands, Le, between two variables or between a variable
and a constant
Ifthe condition i false, then the result is zero (0) and if the
condition is true, then the result is non-zero, These
‘operators are also called relational operators.
Some of the comparison/relational operators used
as follows
OPERATOR DESCRIPTION
= Equal to
. Greater than
< Less than
Greater than or enl to
= Less than oF equal to
)/CBSE_TG) OPERATOR DESCRIPTIO’
or I= Notequal to (not ISO standard)
a Not less than (not 180 standard)
i Not greater than not ISO standard)
Syntax
SELECT |+*|expression
FROM KHERE SELECT EMP_NAME EMP_SALARY
FROM COHPARY
WHERE EMP_SALARY> = 25000,
Rahul Sharma! 25600
| vikes miteel | 26000
Fuaay sing 26000
rows in set (0
When we use relational operators with character data type, <
‘means earlier in the alphabet and > means later inthe
alphabet Bangalore “<" “Braj as “a comes before rin
alphabet.
Logical Operators
‘The logical operators compare two conditions ata time to
determine whether a row can be selected for the output.
Logical operators are also called boolean operators, because
these operators return a boolean data type value as TRUE, or
FALSE,
When retrieving data using a SELECT statement, you can
use these operators in the WHERE clause, which allows you
to.combin
ore than one condition.
Some ofthe Boolean/Logical operators used in SQL are as
follows
OPERATOR DESCRIPTION
AND Logical AND compares two expressions and
‘return true, when both expressions ae tue
on Logical OF compares two expressions and
retim true, when atleast one ofthe
expressions s true
Nor NOT takes single expression as an argument
‘and changes is value fom fle to true or foun
tet false, You can use an exclamation point
()inplace ofthis operator
CBSE Term Il | Computer Science Xil
Syntax
SELECT |*|
FROM
WHERE
For example, query to display EMP_CODE. and
EMP_NAME for those employees whose EMP_DEPT_NO_
is DOB and EMP_SALARY js greater than 22000.
rrysql> SELECT EMP_CODE, EMP_NME
Row cowPaiy
WHERE (ENP_DEPT_NO="DOS" AND
ENP_SALARY >22000)
y produces the following output
ce
Rahul Shara
1 rows in set (0.00 sec)
For example, query to display EMP_CODE and
EMP_NAME for those employees whose
EMP_DEPT_NO is D05 or EMP_SALARY is greater
than $3000.
mysql> SELECT EMP_COOE. EHP_NAME
FROM COMPAR
WIEREC ENP _OEP_NO ~ “DO5" oR
EMP_SALARY>22900)
Above query produces the following output
00 "Rahat Sharma |
For} vikas mictal
loz | Puneet dain
103. | Sachin vats |
Foe} uday singh
105 | Ravi Shukla
106 | Vinay Rana
7 rows 1m set (0.00 sec)
For example, query to display EMP_CODE andl
EMP_NAME for the employee whose EMP_SALARY is
not greater than 22000,
rrysql> SELECT EMP_CODE, EMP_NAME
Row cova
WHERE NOT ENP_SALARY>22000)
rnysql>SELECT EMP_COOE, E4P NAME
row cowpal
WHERE 1(EHP_saLARY>22000) ‘CBSE Term | Computer Science Xil
Above query produces the following output
» CODE + EMP_NAME
Puneet dain
1 row in set (0.00 see)
DISTINCT Keyword
Ina table, some of the columns may contain duplicate values.
‘This is nota problem, mes you may want to
list only the different (distinet) values in a table
‘The DISTINCT keyword ean be used to return only distinet
(different) values in a particular column or a whole table,
Syntax
SELECT DISTINCT colusn_name(s)
FROM table_nare:
eg, If we want to select only’ the distinet values from the
colin named “City” from the PERSONS table.
We have to use the following SELECT states
SELECT DISTINCT City FROM PERSONS
‘The result set will look ike this
Sandnes
Stavanger
ALL Keyword
ALL clause result just the same as that when you do not
specify DISTINCT. twill ive values of selected atibute
from every ww of table without considering the duplicate
records
Syntax
SELECT ALL colun_rane FROM table_nane:
WHERE Clause
‘The WHERE clause is used to extract only those records that
fill a specified criteria
Syntax
SELECT colum_nane(s) FROM table_nane
HERE condition:
eg. If we want to select only the persons liv
“Sandnes” from the table PERSONS,
We use the following SELECT stat
SELECT * FROM PERSONS
WHERE City>" Sandnes:
‘The result set will look like this
rad FirstName
1 Haven Ola
LastNam
2 Svemdkon Tove
Boren 23
Sandnes
Operators Allowed in the WHERE Clause
(i) BETWEEN Operator selects a range of data between
two values. The values can be numbers, text or dates.
Syntax
SELECT coluan_nane(s)
FROW table nae
WHERE column_nane
SETAEEN valuel AND value2
e.g. Ifwe want to select the persons with a last name
alphabetically between “Hansen” and Pettersen’ from
the table PERSONS.
We use the following SELECT statement
SELECT * FROM PERSONS
WHERE Lastname
SETAEEN “Hansen” AND “Pettersen
‘The result set will look like this
Pd LastName FirstName Address
1 Hansen Ola Tin
3 Pettersen Kari Strat 20
ity
eivn 10 Sanden
Stavanger
(ii) NOT BETWEEN Operator selects the data outside
the range of data between two values, The values ean
be text, numbers or dates.
Syntax
SELECT colunn_nane(s)
FROM table nane
WHERE coluiin rane
NOT BETAEEN Valuel AND value2
4 To display the persons outside the range in the
previous example, use NOT BETWEEN operator
‘We use the following SELECT statement
SELECT * FROM PERSONS
WHERE LastName
NOT BETWEEN ‘Hansen’ AND ‘Pettersen’
TThe result set will look like this
pad
2° Svendon
Address Gi
Bonn 23 Sandnes
‘LastName
Name
Tove
(ii) LIKE Operator is used to match a value similar to
specific pattern in a column using % and _. "The % sign
represents zero, one or multiple characters, while _
represents a single character Iti useful when you want
to search rows to match a specife pattern, or when you
do not know the entire value
Syntax
SELECT coluen_nane FROM table_rane
WHERE colum_rane LIKE “condition”
4 To display the FirstName that start with letter"
We use the fallowing SELECT statement
SELECT FirstName FROM PERSONS
WHERE Firstlame LIKE “T"
)/CBSE_TG) ‘The result set will look ike this
FirstName
Tove
64. If we want to display the all records, where the
LastName’s second letter is
We use the following SELECT statement
SELECT * FROM PERSONS
WHERE LastNane LIKE"_at
‘The result set will look like this
PLId LastName FirstName Address City
1 Hansen Ob,
Timoteivn 10 Sandnes
(iv) IN Operator checks a value within a set of values
separated by commas and retrieve the rows from the
table which are matching.
‘The IN operator allows you to specify multiple values
ina WHERE clause.
Syntax
SELECT colum_nane(s)
FROM table_nare
WHERE colum_pare IN (value. value2,...
6 If we want to select the persons with a last name:
equal to “Hansen” or “Pettersen” from the table
PERSONS.
We use the following SELECT statement
SELECT * FROM PERSONS
WHERE LastNane IN
Hansen’ Pettersen’)
‘The result set will ook like this
PLId LastName FirstName Address City
1 Hansen Olt ‘Timoteisn 10. Samdnes
3 Pettersen Kari Storgt 20 Stavanger
ORDER BY Clause
‘The ORDER BY keyword is used to sort the result set along,
specified column with the SELECT command, The
ORDER BY keyword sorts the records in ascending order by
default, you want to sort the records in a descending order,
yout ean use the DESC keyword.
Symtax SELECT colum_nane(s)
FROM table_nane
(ORDER BY cOTunn_namo(s)ASC/DES:
ce, If we want to select all the persons from the table
PERSONS, however, we want to sort the persons by their
last name in ascending order.
We use the following SELECT statement
SELECT * FROM PERSONS
ORDER BY LastName ASC
CBSE Term Il | Computer Science Xil
‘The result set will ook like this
PAd LastName FirstName Address City
1” Hansen Ola
3 Pettersen Kari
2 Swendson
‘Timoteivn 10. Sancies
Storgt 20 Stavanger
‘Tove __Borgyn 23 Sandnes
UPDATE Statement
‘The UPDATE statement is used to update existing records in
atable,
The WHERE clause in the UPDATE statement specifies,
‘which record or records that should be updated. Ifyou omit
the WHERE classe all records will be updated,
[Note fxpressons ae ako used inthe SET claus ofthe UPDATE
‘command manipulate the values.
Syntax (POSTE table_nane
SET columl=valuel, colume=value2,
WHERE FROM
WHERE 1S NULL
duse the
4. Let us consider the following table Teacher
TNo TName TSalary poy
Tw Aradhna 17000 2015-01-08
oe Rita NULL 2015-13-14
03 Ravindrs NULL 2015-11-23
To Dushyat 16000 201401-10
05 Swati 19000 2014-02-10
+ to display column whose value is NULL.
We use the following SQL statement
SELECT * FROM Teacher
WHERE T Salary 1S MULL
Above query produces the following output
To TName ‘TSilary DOJ
Ton Rika NULL 2013-12-14
TUS Ravindra NULL 2013-113,
deleting the
IS NOT NULL Clause
If we want to search the column whose value
Aa table then we use IS NOT NULL clause,
Symtax SELECTecoTunn mane> FROM
WHERE 15 NOT NULL
not NULL in
‘ef. Consider the above table Teacher. Query to display
‘column whose valle is not NULL.
We use the following SQL. statement
SELECT * FROM Teacher
WHERE T Salary 1S NOT NULL
Above query produces the following output
To TName TSalary DOJ
ToL Aradhna 17000 2013-01-08,
TOs Dusbyant__16000 2014.01.10
105 Swat 1000 2014-02-10
Aggregate Functions
Agreaate functions are also known as group functions.
Aggregate functions return a result onlin single row based
‘on group of raws, rather than on single row. Btabways appears
in SELECT command and in ORDER BY and HAVING.
‘clauses. They are commonly used with the GROUP BY
clause in a SELECT statement, Some of the aggregate
fanetions are MIN, MAX, SUM, AVG, COUNT. The
aggregate functions return a single value, ealeulated from
values in a column,
‘There are different types of aggregate functions
AVG)
‘This function returns the average value ofa specified.
column,
Syntax SELECT AVG(colunn_nane)
FROM table_nane;
‘e¢, Consider the following table PAYMENTS:
Empid Emp_Name Salary Department
1 Ridht 20000 DI
2 Roh 25000 pb
3 Rakesh 20000 p2
4 Boshan 44000 DI
5 Rohini 15000 bs
6 Radha 110 DI
‘To display the average of employees salary from PAYMENTS
table.
SELECT AVG(Salary) FROM PAYMENTS:
output. passiueee
AVG(Salan)
25000
)/CBSE_TG) COUNT()
“This fantion returns the total numberof values oF rows of
the specified field or column, COUNT (*) fa special
function, as it returns the count of all rows in a specified
table, Tincludes all he nll and duplicate values,
Syntax
SELECT COWNTC#) FREM table_nane:
To count the total numberof employees from
PAYMENTS table.
SELECT COUNT) “Employees” FROM PRVENTS,
Output
Employees
6
DISTINCT Clause with COUNT() Function
‘The DISTINCT keyword helps us in removing the duplicate
value from the result, When its used with agaresate
finetion COUNT, it returns the number of distinct rows in a
specified table.
Symtax SELECT COUNTCOISTINCT colunm_pare)
FROM table_nane:
4 To count total number of rows in Department column
from PAYMENTS table.
SELECT COUNT( Department) “Dept a
FROM PAYMENTS
output
Deptid
6
64 To count distinet values of column Department from
PAYMENTS table
SELECT CONT(DISTINC Deprtnent) “Deptic
FROM PAYMENTS:
utp ——
Deptid|
3
MAX()
‘This function returns the largest value from the selected
column,
Symtax SELECT MAX(coTun_nane)
FROM table_nane:
6 To find the maximum salary of employee from
PAYMENTS table,
SELECT MAK(S@lary) “Waxinun”
FROM PAYMENTS:
Output oe
‘Maximum
“44000
CBSE Term Il | Computer Science Xil
MIN()
“This fanetion returns the smallest value from the selected
column,
Syntax SELECT MINcolum_nane)
ROH table ane:
4, To display th
PAYMENTS table
SELECT MINCSalary) “Minima
FROM PAYMENTS:
Output
Minimum
74000
sUM()
‘This funetion returns the sum of values in the specified
‘column, The SUM works on numeric fields oly. Null values
«are excluded from th
Syntax SELECT SUM(colum_nane)
FROM table_nane:
result returned.
‘eg To count sum of employee's s
table,
SELECT SUM(Salary)
FROM PAYMENTS:
Output
ary from PAYMENTS
‘Salary
Salary
138000
GROUP BY Statement
‘The GROUP BY statement is used with the aggregate
functions to group the result set by one or more col
Syntax SELECT coluen_nane,
aggregate_function(colum_rane)
FROM table_nane
WHERE condition
(GROUP BY colunn_nane;
Consider the following ORDERS table
TABLE: ORDERS
O1d OrderDate _ OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2068/1098 1600 Nilen
20080002 TOO Hanses
4200590008 80) Hanes
5 2000 2000 Jensen
6 2008/1004 10 Nilsen
‘eg. Ifwe want to find the total sum (OrdesPrice) of each
‘customer from table ORDERS.
)/CBSE_TG) (CBSE Term I!| Computer Science Xi
We use the following SQL statement
SELECT Customer, SLM(OrderPrice)
FROM ORDERS
GROUP BY Customer:
‘The result set will look like this
Customer __ SUM(OrderPrice)
Hansen 2000
Niken 1700
Jensen 3000
Let's see what happens if we omit the GROUP BY statement
SELECT Customer, SIM(OrderPrice)
FROM ORDERS:
‘The result sot will look like this
SUM(OrderPriee)
5700
Customer
Hansen
In the above ease, SELECT statement cannot be used,
because the SELECT statement above has two columns
specified (Customer and SUM(OrderPriee)). The
“SUM(OrderPrice)” returns a single value (that is the total
sum of the ““OrderPrice” column), while “Customer” returns
6 values (one value foreach row in the “ORDERS” table),
This will therefore not give us the correct result. However,
you have seen that the GROUP BY statement solves this
problem.
HAVING Clause
‘The HAVING clause is used with GROUP BY ease to place
condition because the WHERE keyword could not be used
with agaregate functions.
Syntax
SELECT colum pane
aggregate_function(colum_nane)
FROM table_nare
HERE condition
GROUP BY coTurn_name
HAVING aggregate_functon(colr rane)
condi tio
ex, If we want to find any of the customers have a total order
of less than 2000 from table ORDERS.
We use the following SQL statement
SELECT Customer. SIM(OrderPrice)
FROM ORDERS
GROUP BY Custoner
HAVING SUM(OrderPrice)<2000:
‘The result set will look ike this
Customer SUM|OrderPrice)
Nilsen 170
eg, Ifwe want to find the customers “Hansen” or “Jensen!
have a total order of more than 1500,
We add an ordinary WHERE clause to the SQL statement
SELECT Customer. SUM(QrderPr:
FROM ORDERS
WHERE Custaner="Hansen’ OR Custaner=" Jensen
GROUP BY Custoner
HAVING SUM(OrderPrice)=1600:
‘The result set will look like this
Customer SUM(OrderPrce)
Hansen 2000
Jer 2000
Join
A join isa query that combines rows from two or more tables
Inajoin query, more than one tables ae listed in FROM
‘ase The function of combining data from multiple tables
called joining, SQL can obtain daa from several related tables
by performing either a physical or virtual join on the tables
Joins are used when we have to select data fom two or more
tables. Joins are used to extract data from two (or more)
tables, when we need a relationship between certain columns
in these tables.
“The SOL Join condition i always used in the WHERE,
case of SELECT, UPDATE and DELETE statements
‘There are different kind of QL. joins
Equi Join
Equi join is simple SQL. jon condition that uses equal sign
as comparison operator
Syntax. SELECT coll, col2,c013
FROM tabtel, table?
WHERE tablelcoll = table2. coll
Consider the following tables PERSON and ORDERS
Table : PERSON
P1d_Last Name First_Name City
1 Sharma Abhay Mumbai
2 Guta Mohan Delhi
3 Verma AK Mumbai
‘Table : ORDERS.
4d Order_No pad
1 10050 3
2 25000 3
3 6st 1
4 4500 1
5 35000 15
‘64g. To join two tables PERSON and ORDERS using @
condition (ie. P_ld of PERSON table is equal to the P_Id of
ORDERS table}
)/CBSE_TG) We use the following SQL statement
SELECT LastName, FirstName, Order No
FROM PERSON, ORDERS
HERE: PERSON, P_Id-ORDERS.P_Id
(ORDER BY PERSON, Last_Nane
‘The query will give us the below result
Last Name Fit Name Order No
sharma Abhay ‘5687
sharma Abhay 45000
Verma Akhil 10050
Verma Akhil 25000
SQL equi joins are further clasified into two categories
() Inner Join
‘The inner join using ether ofthe equivalent queries gives
the intersection of two tables, i. it returns us the rows,
‘hich are common in both the tables
Syntax
SELECT coll, col2
FROM tablel INNER JOIN table2
OW tablel column nare-table2.colim_nane
64 To perform INNER JOIN on two tables PERSON and
ORDERS.
We use the fallowing SQL statement
SELECT Last_Nane, First_Nome FROM
PERSON INWER JOIN ORDERS 8
PERSON.P_L4-ORDERS. Id
“The result set will lok like this
Last Name First_Name
Verma AK
Verma Akhil
Sharma Abhay
Sharma Abhi
(ii) Outer Join
‘The outer join include rows in a joined result even when
they have no match in the joined table. An outer join returns
all rows that satisfy the join condition and also returns those
rows from one table for which no rows from the other satisfy
the join condition,
Left Outer Join The left outer join returns all the rows from
the let table, even ifthere are no matches in the right table.
Syntax
SELECT colum 1, colum 2
FROM tablel LEFT JOIN table? ON
tablel.colum_nane ~ table2.colum_nane:
‘ex. To perform LEFT OUTER JOIN on two tables
PERSON and ORDERS.
We use the following SQL statement
CBSE Term Il | Computer Science Xil
SELECT P.Last_Nane
P.Fiest_Nane,0.Order_No
FROM PERSON P
LEFT JOIN ORDERS 0
OW P.P_Id = 0.P_Id ORDER BY
P.First_Nane!
‘The query will give us the below result
Last_Name FirstName Order_No
Sharma Abhay 5687
Sharm Abhay $5000
Verma Akhil 2500
Verma Akhil 10050
Gupta Mohan
Right Outer Join ‘The right outer join returns all the rows
from the right table, even if there are no matches in the left
table.
Syntax
SELECT coluenl., column?
FROM tablel RIGHT JOIN table2
(N tablel .colunn_nane-table2.colun_nane:
‘e.g. To perform RIGHT OUTER JOIN on two tables
PERSON and ORDERS,
We use the following SQL statement
SELECT P.Last_Nane, P.First_Name.
(0.Order_No FROM PERSON RIGHT JOIN ORDERS
OW P.P Id = 0.PLId
(ORDER BY P, LastName:
‘The query will give us the below result
‘Last_Name First Name Order_No
Sharma Abhay 45000
Sharma Abhay 3887
Verma Aklul 2500
Verma AKI 10050
35000
Self Join
A self join isa join, where we join a particular table to itself,
Here in this ease, it is necessary to ensure that the join
statement defines an ALIAS name for both the copies of the
tables to avoid column ambiguity. Consider the following
table COURSE:
‘Table : COURSE
Course Name Pre.
‘Course td
©
2 cH L
3 Java 2
4 cH 3
5 VBNET 3 ‘CBSE Term | Computer Science Xil
To perform SELF JOIN on COURSE table,
We use the following SQL statement
SELECT a.Course_Name AS COURSE
b.Course Name AS Prerequis
FROM COURSE a COURSE D
MERE 0. re_Course = b,Course_t
The result set will look like this
COURSE Prerequisite Course
cH
Joa cee
c# Joa
VBNET Java
Non-Equi Join
Non-equi join is used to return the result from two oF more
tables, where exact join is not possible. The SQL non-equi
join uses comparison operators instead of, the equal sign like
>,<.>=,<= alongwith conditions
Syntax
SELECT * FROM tablel. table?
WERE tablel.colum > table2.colum:
Consider the following tables
Table: EMP
fempno__ename sl date
10 Pea 000 1-11-2010
ML Seema 14000 15.02.2014
151 Sachin 30000 18-04-2015
12 Deepa 25000 20-05-2015
Table : SALGRADE
‘empno ity lowsal —hisal grade
Lio Delhi 5000 100002
mu NER 11000 190001
1a Meerut 10000 30005
eg. To perform NON-EQUI JOIN on two tables E
SALGRADE,
Wo use the following SQL statement
SELECT e.enpno, e.ename, e.sal
FROM BHP ©, SALGRADE 5
HERE sal BETWEEN s.Tonsal AND s.hisal
‘The result set will look like this
s.grade
fempno ename sal grade
10 Priya TOD
ut Scema 140005
Natural Join
‘The natural join isa type of equi join and is structured in
such a way that, columns with same name of associated tables
will appear once only
Syntax
SELECT * FROM tablet
NATURAL JOIN table2:
Consider the following tables
Table
Foops
Iem_ld ItemName Item Unit Company td
1 Chex Mix Pes 16
6 Cheenit Pes 6
2 Biscuit Pos 15
3 Munch Pes 7
4 Rice Pes 5
5 Cake Pes Is
Snake Pes NULL.
‘Table : COMPANY
Company fa Company_Name Company City
18 Order all, Boston
15 Jack Mill Le London
Alas Foods Delhi
Foodies London
Sip_n_Bite New York
646 To fetch all the unique columns from “FOODS' and.
‘COMPANY tables, alter joining these tables,
We use the following SQL statement
SELECT * FROM FOODS NATURAL JOIN COMPANY WHERE
FOODS. Conpany_Id-CONPANY.Compary_I:
‘The result set will look like this
Hem_ Ktem_Item_ Company_ Company, Company
Md Name Unit dd Name ty
1 Chex Pes 16 Akw Delhi
Mis Fonds
6 Cheezit Pes 15 Jack Hill London
tnd
2 Biscuit Pes 15 Jacki London
Ua
Munch Pes 17 Foodies Lond
Rice Pes 15 Jack Hil London
Ud
5 Cake Pes 18 Onder All Boston
)/CBSE_TG) Chapter
Practice
PART 1
Objective Questions
* Multiple Choice Questions
1. Which of the following is/are advantage(s) of SQL?
(a) igh speed (b) Clientserver language
(¢) Rasy to learn (@) Allof these
Ans. (d) High spced, clientscrver language, easy to learn and
understand are advantages of SQL. Its portable and
used for relational database
2. Which of the following command is used to remove
the table definition and all data?
(a) CREATE
(@ DROP
PROP command is used to remove the table
definition and all data
Ans.
3. Which of the following isu eorreet syntax to add a
column in SQL command?
(a)ALTER TLE table_nane ADD column nane
data_type:
(WALTER THBLE AD coluan rane data
(@)ALTER table_rane AOD column rane
data_type:
(€) None ofthe above
Ana.) Syntax toad a column in SQI-command is
ALTER TABLE table_nane ADD colunn_nane
data_type
4. Which keyword can be used to return only
different values ina particular column or a whole
table?
(@ WHERE () DistINCr
(ALL (a) BETWEEN
Ans. (b) DISTINCT keyword can be used to return only
diferent values ina particular column or a whole table
The keyword sorts the records in ascending
order by default,
(@ ut (b) UPDari
(ORDER (d ORDER BY
Ang.) ORDER BY keyword sorts the records in ascending
‘order by default,
6. ‘The clause used to cheek NULL values is
fi) IS NULL
() IS NOT NULL
(6) Both fa) and
(@ None ofthe above
Ans.(a) The IS NULL clause is used to cheek NULL values in
afield.
7. The operator is used for pattern matching,
) BETWEEN () LIKE
(mn (@) LOOKSLIKE
Ans.) The LIKE operator is used to match patterns ina
fel
8, To delete all the records fom a table “Product” the
command will be
(@) EL FROM Product:
(D) DELETE FROM Produ
(©) REYOVE ALL FROM Product
(@)OELETE ALL
Ana.) To delete all th reords from a table
command wil be
DELETE FROM Product
9. The character displays all the columns of «
table in a SELECT query.
Product” the
we ne or @
Ans.(c) The * character displays all the columns in a SELE
query
10. ‘The command removes a table completely
(a) DELETE. () REMOVE
(9 DROP (@) UPDATE
Ans.(c) The DROP command removes a table completely
alongwith its dat
11. The "SET" clause is used along with
() DESCRIBE
(@) UPDATE.
Ans. (d) The UPDATE command updates data ofa table. 1b
uses the “SET” clause to specify the field to be updated
)/CBSE_TG) ‘CBSE Term | Computer Science Xil
12, What is true about the following SQL statement?
riysql> SELECTFFROM Student (scent)
{@) Displays contents of table ‘Student
{) Displays column names and contents of table
‘Student
(oR
(4) Displays only the column names of able “Student”
Ans.(b) The command displays entire contents ofthe table
along with column names.
ts in error as improper ease has heen used.
13. Which operator is used to compare a value to a
specified list of values?
(ANY (b) BETWEEN
(ALL in
Ans. (d) The IN operator easily tests the expression, it
matches any value in a specified list of value
Which of the following is the correct order of a
SQL statement?
(@) SELECT, GROUP By, WHERE, HAVING
(b) SELECT, WHERE, GROUP BY, HAVING
(¢) SELECT, HAVING, WHERE, GROUP BY
(d) SELECT, WHERE, HAVING, GROUP BY
Ans.(b) In SQL statement, the WHERE clause always comes
before GROUP BY and HAVING clause always comes
ater GROUP BY. Hence, option (b) is correct.
15. ...... provides statements for creation and delet
of the database tables, views.
(@ DDL. () DEL
fe) DML Ten.
Ans. (a) DDL. (Data Definition Language) provides statements
for ereation and deletion of database tables, views,
16. Which of the following is not an aggregate
function?
(avon (by ADD)
fe) MAX) (county
Ans.(b) There is no aggregate function named ADDO but
SUMO isan azsregate function whieh performs
‘mathematical sum of multiple rows having numerical
values
17. Which aggregate function returns the count ofall
rows in a specified table?
SUMO (») Distinct)
(e) COUNT), (@) None of these
Ans.(c) COUNT?) function returns the total number of values
or rows ofthe specified field or column,
18. In which fanetion, NULL values are excluded from
the result ret
(SUMO
(©) MING)
med?
() Max
(All of these
‘Ans.(d) NULL values are excluded from the result returned
by al the agarezate functions
19, The AVG) fimetion in MySQL is an example of
(@) Math fanetion (0) Text fanetion
(© Date funetion (4) Agareyate function
Ans. (d) The AVG) fanction returns the average
column or rmitiple=rows,
So, the AVG () funetion in MySQL. isan example of
augrezite function
te froma
20. Which of the following fumetion count all the values:
except NULL?
(@) COUNT)
(6) COUNT(columm_name)
(Q COUNTINOT NULL)
(@ COUNTINULL)
Ans.) All aggregate functions exclude NULL values while
‘performing the operation and COUNT)*) is an aggregate
Faneto
21. What is the meaning of “GROUP BY” clause in
MySQL?
(@) Group data by column values
() Group data by row values
(6) Group data by column and row values
(4) None ofthe mentioned
Ans.) Through GROUP BY clanse we can create groups
fiom a column of data in a table
22. Which clause is similar to “HAVING” elause in
MySQL?
(@) SELECT () WHERE
(FROM (@) None ofthe mentioned
Ang.(b) HAVING clanse will act esaetly sane as WHERE,
clause, i. filtering the rows based on certain conditions
23, Which clause is used with an “aggregate
funetions"?
(@) GROUP BY (0) SELECT
(WHERE (4) Both (a) and (e)
‘Ans.(a) "GROUP BY” is used with an aggregate functions,
24. Which of the following join gives the intersection
of two tables?
(a) Outer join (@) Inner join
(6) Equi join (@) None ofthese
Ang.) Inne join gives the intersection of two tables.
25. isa simple SQL join condition that uses
equal sign as a comparison operator.
(@) Equi join () Non-equ join
(6) Both (a) and (@) None ofthe above
Ans. (a) Equi join isa simple SQL. join condition that uses
I sign asa comparison operator.
)/CBSE_TG) * Case Based MCQs
26. Direction Read the case and answer the following
‘questions
Ronita wants to store the data of some products in a
table product as follows
Table : Product
PNo PName Quy Date_OF Mfg
pow Pencil 20 2020-00-01,
Pox Ener 5 1900-00-11,
Pos Book 6 2000-04-08
Pow Notebook 15 2016-12-11
Pos Color 10 2015.02.04
She also wants to perform some operations and
manipulations on the table . Help her to find the
solutions of following questions.
(i) A command that displays the details ofall the
products will be
(@) SELECT * FROM Product
{) SHOW * FROM Product
{DISPLAY * FROM Product:
(@)SELECT ALL details FROM Product:
(ii) The default date format in which date has to be
stored in MySQL is,
(@ DD-MM-YYY, () DD
fo MMYY-DD @yy-MM-DD,
(Gi) Which command she can use to add a new col
to the table?
(@) INSERT (h) UPDATE,
(@ ADD COLUMN (ALTER
(iv) Suggest her a proper data type for the “PName”
‘column,
(@) Varchar
() Double
(Float
(4) Integer
(¥) She is confused whether she has to use the
“COLUMN” clause with the ALTER TABLE,
‘command to add a column to the table. What
should she do ?
(@) COLUMN clanse is must
(0) COLUMN clause is optional.
(© COLUMN clause is must for adling integer columns
only
{) None ofthe shove
Ans. (i) a) SELECT * FROM Product:
i) (a) By default, MySQL stores dat
format,
Y-MM-DD.
CBSE Term Il | Computer Science Xil
(it) (d) The ALTER command can be used to make any’
‘changes tothe structure of «table.
(iv) (a) The varchar isa variable length datatype that
‘can used for columns storing stringicharacter type of
(0) (b) With the ALTER TABLE command the
COLUMN clause is optional, in adding columns to a
table,
27. Table: Book_Information
Column Name
‘Table: Sales
Column Name
Book 1D Store 1D
Book Title Sales_Date
Price Sales_Amownt
Basis on above table information, answer the
following questions
(i) Which SQL statement allows you to find the
highest price from the table Book_Information?
(a) SELECT Book_{D.Book_Title.MAK(Price) FREM
Book_Information:
{b) SELECT MAKCPrice) FROM Book_Infornat ton:
(6) SELECT MAXIMIMCPrice) FROM Bock_Informat on
(4) SELECT Price FROM Book Information ORDER BY Price
se
(ii) Which SQL statement allows you to find sales
amount for each store?
SELECT Store 1D. SUM(Sales_Amunt) FROM Sates:
SELECT Store_1D. SUM(Sales_Amaunt) FROM Sales
(FOER BY Store_I0:
() SELECT Store_1D. SUM(SaTes_Amaunt) FROM Sales
GROUP BY Store_I0:
(q) SELECT Store_1D. SUM(Sales_Amunt) FROM Sales
HAVING UNIQUE Store_10
(ii) Which SQL statement lets you to list all store name
whose total sales amount is over 5000 ?
(a) SELECT Store_1D. SUM(SeTes Amount) FROM Sales
GROUP BY Store_iD HAVING SUM(Sales_Anount) >
5000
(h) SELECT Store_1D. SUMSales_Anount) FROM Sales
GROUP BY Store_iO WAVING Sales. Amount. > 5000:
(6 SELECT Store 1D. SUN(Sales_Ancunt) FROM Sales
WERE sum(Saies_Anount) > 5000 GROUP BY Store_1:
(2) SELECT Store_ID. SUMSales_ Amount) FRM Sales
WHERE Sales Aunt > 5000 GROUP BY Store_1o
(iv) Which SQL state 1 find the total
number of stor tuble?
w)
nt lets
in the SAL
)/CBSE_TG) ‘CBSE Term | Computer Science Xil
(0) SELECT COUNTY Store_ 10) FRM Sales
(by SELECT CQUNT(OISTINCT Store_1D) FROM
Sales:
(© SELECT DISTINCT Store_IO FROM Sates:
(@) SELECT COUMT(Store_ 10) FROM Sales GROUP BY
Store_10;
(6) Which SQL statement allows you to find the total
jount for Store_ID 25 and the total sales
amount for Store_ID 45?
Store 1D IN ( 25,
(b) SELECT Store_1D, SUM(Sa1es_Anount) FROM Sales
‘GROUP BY Store_ID HAVING Store_ID IN ¢ 25, 45)
(© SELECT Store 1D, SUM(Sales_Anount) FROH Sales
WHERE Store ID IN (25.45)
(@) SELECT Store 1D, SUM(Sa1es_Anount) FROM Sales
WHERE Store ID 25 AND Stone_I0 ~45 GROUP BY
Store 0:
‘Ans, (i) (b) SELECT YAK(Price) FROM Book _Informatin
(i) (@) SELECT Store_1D, SUM(Sales_Asount) FROM Sales
GRO BY Store
(Ui) (a) SELECT Store 10, SUM(Sales Amount) FROM Sales
GROUP BY Store_[D HAVING SUM(Sales Aenunt) >
5000
(iv) (@) SELECT CoUNT(Store_D) FROM Sates GROUP BY
Store 1
(©) () SELECT Store_ID, SUM(SaTes Amount) FROM Sates
GROUP BY Store ID HAVING Store ID IN ( 25
48)
AT
Subjective Questions
© Short Answer Type Questions
1. Differentiate between ALTER and UPDATE
‘commands in SQL. (NCERT)
‘An Diforonces between ALTER and UPDATE commands
in $Ql.are
ALTER command UPDATE command
belongs to DDL,
ceatexory
Itbelongs to DML category
Te changes the structure toads data ofthe table
ofthe table,
Columns ean be added,
modified deleted ot
Data ean be changed updated
with values and expressions,
2, Mow is char datatype different from varchar data
type? (CERT)
Ans. Differences between char datatype and varchar data type
Char Varchar
Wistived length, Tes variable length
‘Wastage of memory. Memory usage any as per data size.
Less wef Better data ype
3, Explain the use of ORDER BY clause.
Ang. The ORDER BY clanseis used to arrange the reeords in
ascending or descending order. Data present in a table
‘ean be arranged as per requirement on a specific eld in
ascending or descending order. The default is aseending
‘order. To arrange in descending order the DESC clause
is tobe used. To arrange in ascending order ASC may be
used.
ce SELECT * FROM Employee CROER BY HP_SALARY DESC
‘The above command arranges the records in descent
‘order of salary.
4. Write the queries forthe following questions using
the table Product with the following fields
(P_Code, P_Name, Qty, Price)
(0) Display the price of product having code as PO6.
(i) Display the name ofall products with qu
sreater than 50 and price less than 500.
‘Ans. () SELECT Price FROM Product. WHERE
P Codle="PO6"
‘The ertera of the records that ae to be displayed
‘ean he specified with WHERE clause of SQL.
(i) SELECT P Name FROM Product WHERE Qty>50 AND
Price 50 and price <3)
5. 1s it compulsory to provide values for all columns
ofa table while adding records? Give an example.
Ans. No ts not compulsory to provide values forall columns
ofa table while adding records, We ean wse NULL
sales wherever vals are misting
eg INSERT INTO Employee VALUES
‘GML, “Sates 83000)
6. Amit wrote the comman
Student” as
CREATE TABLE Student(Ro1TNo, integer, Name
varchar(20). Marks float(8.2))
‘What does (8,2) mean hero?
Ana While specifying float columns ina table the width and
the number of decimals have to be specified. Here 8 is
the total width and 2is the number of decimal places for
the Marks column,
tocereatea table 7. Rakesh wants to increase the price of some of the
products by 20% , of his store whose priee is less
than 200, Assuming the following structure , what
will be the query?
PNo Name Quality Price
‘Ans. UPDATE ITEM SET Price=Price + Price * 0.2 WHERE
Price<200
‘The UPDATE command updates data ofa table. While
updating, the expression for update value can be
assigned to the updating field. The records to he upsated
tan be specified as WHERE condition,
8 Write the use of LIKE clause and a short
explanation on the two characters used with
Ans. This operator is used to search a specified pattern in a
column, THis usefl when you want to search rows to
match a specifie pattern oF when you do not know the
entire valueThe SQL. LIKE clase is used to compare a
value to similar values using wildeard characters
We describe patterns by using two special wildeard
characters, given below
(i) The per cent sian () is used to match any
substring,
(i) The underscore (is used to mateh any single
charset.
“The symbols ean also be used in combinations
Given the command below:
DELETE FROM Toys WHERE Toylane LIKE
Which records will be deleted by th
‘command?
Ans. The command has a LIKE clause with “$_18 which
means all the toy names that start with the Tetter'S"and
has Sr letter ast will deleted.
10. In the followin
deleted?
DELETE Student
WHERE Student_10°109;
(Assuming a Student table with primary key
Student_ID)
‘Ans. 0ELETE FROM Student: MHERE Student_ID-Lo8:
ere, the “FROM” clause is missing , so the command
will produce an erro
i
‘above
query how many rows will be
(NcERr)
11. if the value in the column is repeatable, how do
you find out the unique values? (NcERF)
Ans. The DISTINCT clause in SQL is used to display only
distinct values ina column of table. Hence, ifthe
column allows duplicate values the unique values ean be
extracted using the DISTINCT clause.
CBSE Term Il | Computer Science Xil
SELECT DISTINCT CLASS FROM Student
“This displays only the unique classes.
12, What do you mean by an operator? Name any four
‘operators used in queries
‘Ans. An operator i component of an expression that
represents the action that shouldbe taken over a st of
values.
Four operators used in queries are
(i) Arithmetic operators
(i) Comparison operators
(i) Boolean/Lowieal operators
(iv) Between operator
13, How NOT operator is used with WHERE clause?
Give an example,
Ans.'The WHERE clause is used to retrieve some given data
according tothe eondition and NOT operator reverses
the result oft
For example
nysqi>SELECT Nane, Class. Games FROM
Student_fable MERE NOT Ganes = “FeotBAL
14, What are the fnetions of ALTER TABLE
command?
‘Ans,'The man functions of ALTER TABLE command are
(@) Add or drop columns,
(i) Change the column definition of a column,
(i) Add or drop constraint
(iv) Rename a column,
15. Write syntax ofthe conditions given below.
(i) Add a col
(ii) Delete a column from a table.
‘Ans, () ALTER TABLEstable_rane>400
datatypesval >
(i) ALTER TABLEDROP
COLUMcoluun_nane>:
16. Consider the following table PREPAID. Write
MySQL commands for the statements given below.
atable,
S.No ©Name Model Connection
1 Sita Nokia Alitel
2 Govt Samsung Mea
3. Ritesh Le BSNL
4 Jayant Micromax Reliance
() DELETE a column name Model
(i) DELETE a customer record where connection
type is BSNL.
‘Ans, () mysql> ALTER TABLE PREPAID OROP Hode)
(i) mysql> DELETE FROM PREPAID WHERE Connection =
SNL
)/CBSE_TG) ‘CBSE Term | Computer Science Xil
17, What will be the output ofthe following queries on
the basis of EMPLOYEE table?
‘Table : EMPLOYEE,
Emptd Name Salary
FOL Siva 54000
02 oy NULL
03 ‘Allen 32000
ot Nee 42000
(i) SELECT Salary + 100 FROM EMPLOYEE
WHERE Enp_Id = “£02
(li) SELECT Nane FROM EMPLOYEE
WHERE Enp_Id = “E04:
Ans. The output ofthe following queries
Salary +100
@ Sw
NULL
Name
Neev
18. What are the aggregate funetions in SQL?
Ans. Azsrexate function is fanetion where the values of
rultiple-rows are grouped together as input on certain
eriteria to forma single value of more si
meaning, Some agerezate functions
SUM (), AVG(), MINO, ete.
19. What is the purpose of GROUP BY clause in
MySQL? How is it different from ORDER BY
clause? [CBSE 2012]
‘Ans. The GROUP BY clause can be used to combine al those
records that have idential value in a particular eld ora
sroup of fel
‘Whereas, ORDER BY clause is used to display the
records either in ascending or descending order based on
4 particular fied. For ascending order ASC is used and
for descending order, DESC is used. The default order is
ascending order
in SQL are
8
Gopi Krishna is using a table Employee. It has the
following columns
Code. Nane, Salary, Dept code
He wants to display maximum salary department
wise. He wrote the following command
SELECT Deptcode, Max(Salary) FROM Employee:
But he did not get the desired result
Rewrite the above query with necessary changes to
help him get the desired output. (CBSE Delhi 2014]
Ans. SELECT Deptcode, Max(Salary)
FROM Employee
GROUP BY Deptcode:
Jonsider the following table Employee
Table : Employee
at.
10 Steven King Shing 1057.06.17 AD_PRES 2000000 90
101 Neona Kochhar Nkair 1967-0618 AD_VP
102 Lex De Ham Lean 1987-0619. AD_VP 900000
1M Alesunder Hold Ahold 1987-0620 FT_PROG eID” 6
104 Bruce Est Bernt 1987-0821 TT_PROG 480000 6
105 David Austin Dansin 1987-0522 si.00 6
106 Vall) Fatal Vp 1887-0823 TTPROG 450000 100,
balla
Write a query to get the total salary, maximum,
tninimum, average salary of employees Gob_1D
wise), for Dept_ID 90 onl:
Ans SELECT Job 1D, SUMSaTary)
max(Salary), MINGalary)
FROM Employee
WHERE Dept_ID = “90
GROUP BY Job 1:
* Long Answer Type Questions
22. What is the differences between HAVING clause.
and WHERE clause?
Ans. Differences between HAVING clause and WHER
clause are
AVG(SaTary)
WHERE clause HAVING clause
WHERE else is use to
{ler the records from the
table based om the specified
condition
HAVING clases sed to
Alter record fon the groups
Dased on the specified
cone,
HAVING clase implements
in column operation.
WHERE clase
plements in row
operation,
WHERE clause cannot
contain agaveyate function
WHERE chase ean be
HAVING clase can contain
great function
HAVING clase ean only be
ved with SELEC used with SELECT statement
UPDATE, DELETE
statement,
WHERE clase s used HAVING clause ts used with
with single row Rinction multiple rv fanetion like
like UPPER, LOWER ete. SUM. 23. Answer the questions (i) to () on the basis of the
following tables SHOPPE and ACCESSORIES.
Table : SHOPPE
ia SName Ares
‘S001 ABC Computeronies P
‘S002 All Infotech Media cK
‘$003 Tech Shoppe cr
S001 Geeks Teen Soft Nehmu Place
005 Hitech Tech Store Nehru Phe
Table : ACCESSORIES
No Name Price a
AO} Mother 12000 sol
Board
ame Hard Disk 5000 soL
a0 Keyboard 500 ‘soz
08 Mouse 300 sol
AUS Mother 13000 ‘soz
Board
06 Keyhourd 400 03
aor Lep 6000 so
‘Tos a 500 05
‘109 Mouse 350 $05
no Hard Disk 4500 03
(i) To display Name and Price ofall the Accessories
in ascending order of their Price.
(ii) To display Td and SName of all Shoppe located in
Nehru Place.
(ii) 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,
(©) To display name of accessories whose price is
seater than 1000,
(SELECT Name, Price
FROM ACCESSORIES
ORDER BY Price
(i) SELECT 14, SHane
Ans.
FROM SHOPPE.
WHERE Area = ‘Nehru Place
(i) SELECT MIN(Price) “Minimum Price”
wax(Price) “Maximum Price”, Name
FROM ACCESSORIES
GROUP BY Name:
CBSE Term Il | Computer Science Xil
(iv) SELECT Name, Price, SNane
FROM ACCESSORIES A, SHOPPE $
WHERE A. Td = Id
but this query enable to show the result because
Ald and Sid are not identical,
(9) SELECT Nane From
ACCESSORIES
WHERE Price>1000
24. Consider the following tables STORE and answer
the questions:
Table: STORE
emo Item Seode Qty Rate LastBuy
2005 Sharpener Classe 2360S SIJUN.OD
2005 Balls 2 50 25 OLFED-I0
2002 Gel Pen Promiun 2115012 2LFEB.I0
2006 Gel PenClasic 3125020 TLMAROO
2001 Eser Small 2 20 6 IWJAN-09
2004 Eraser Big 2 08 oR DEC
2009 Hall Pen 05 at 1018 axNOV.M
Write SQL commands for the following statements:
() To display details of all the items in the STORE,
table in ascending order of LastBuy.
(i) To display ItemNo and Item name of those
items from STORE table, whose Rate is more
than @15,
(ii) To display the details of those items whose Supplier
code (Scode) is 22 oF Quantity in Store (Qty) is
‘more than 110 from the table STORE,
(iv) To display minimum rate of items for each
ipplier individually as per Seode from the table
STORE.
(0) To display the item with its quantity which
inelude pen in thei
Ans, (i) SELECT * FROM STORE ORDER BY LastBuy
i) SELECT Ttemto, Item FROM STORE WHERE Rates
(Gi) SELECT * FROM STORE WHERE Scode = 22 OR
aty>1i0.
(jv) SELECT MIN(Rate) FROM STORE GROUP BY Scode.
() SELECT Ite, Qty FROM STORE WHERE Item LIKE
‘ent
25. Consider the following tables STUDENT and.
STREAM. Write SQL commands for the
statements (i) to (v).
)/CBSE_TG) (CBSE Term I!| Computer Science Xi
‘Table : STUDENT
SCODE NAME AGE STRODE POINTS GRADE
ol Amit 16 1 6 NULL
102 Arjun BS 4 NUL
103 Zaheer 4 2 1 NULL
5 Gan BS NULL.
WS Kumr 13 NULL.
9 Rajesh IT NULL
HO Nween 13 NULL,
113 Ay we 2 NULL.
115 Kop Moos NULL.
120 Gurdeep 152 6 NULL.
le : STREAM
STRCDE, STRNAME
1 SCIENCE+COMP.
NCE4+BIO|
3 SCIENCE+ECO
4 COMMENCE+MATH.
s
5 COMMERCE+SOCIO
6 ARTS+MATHS
ARTS#S0€10
(i) To display the name of streams in alphabetical
order from table STREAM.
the number of students whose
wre more than 5.
(ii) To update GRADE. to‘ for all those students,
who are getting more than 8 as POINTS.
(iv) ARTS+MATHS stream is no more available.
Make necessary change in table STREAM.
(0) To display student's name whose stream name is
seience and computer,
Ans. (i) SELECT STRMMME FROM STREAM ORDER BY STRNAME:
Gi) SELECT COUNTC*) FROM STUDENT WHERE POINTS > 5:
(Gi) UPDATE STUDENT SET GRADE = “A” WHERE POINTS >
8
(iv) DELETE FROM STREAM WHERE STRNIME
= ARTS + MATHS:
(0) SELECT NAME FROW STUDENT
LMHERE STUDENT STRCDE = STREAM, STRCDE
‘MID STRUAE = “SCIENCE + CONP™
ny, tables GARMENT and
2. Write SQL commands for the statements
26. Consider the follow
FABRI
(to(),
‘Table : GARMENT
GCODE DESCRIPTION PRICE FCODE READYDATE,
10023 PENCILSKIRE 1150 FU3—_IDEC-05
10001 FORMALSHINT 1250 FOL 12JaN0s.
W012 INFORMAL. 1550 FOE OBJUN-S
SHIRT
10021 BABY TOP 750 FOS OAPROT
10090 TULIP SKIRT 350 FW SLMAR.OT
10019 EVENINGGOWN $50 FOS OBJUN-OS
10009 INFORMAL 1500 PUB -20.0CT.08
PANT
10007 FORMAL PANT 1350 FOL MAR.OS
10020 FROCK 550 FOS OW SEP-OT
10089 SLACKS: 750 FS BAOCTAS
‘Table : FABRIC
FCODE TYPE
Pos POLYSTER
Fu COTTON
Fux SILK
POL TERELENE,
(0)To display GCODE and DESCRIPTION ofeach
GARMENT in descending order of GCODE.
(ii) To display the details ofall the GARMENT,
which have READYDATE in between
08-DEC-07 and 16-JUN-0S (inclusive of both
the dates)
(ii) To display the average PRICE ofall the
GARMENT, Which are made up of FABRIC
with FCODE as P03.
(iv) To display FABRIC wise highest and lowest
price of GARMENT from GARMENT table,
(Display FCODE of each GARMENT alongwith
highest and lowest price.)
(6) To display garment’s description with their price
whose fabric is silk
‘Ans, (i) SELECT GCODE, DESCRIPTION FROM GABWENT ORDER
By CODE OESé:
(i) SELECTFROM GARMENT
HERE READYOATE BETWEEN “08-DEC-07° AND
16-JN-08
}) SELECT AVOCPRICE) FROM GARMENT MERE FCOOE =
FOS)
)/CBSE_TG) (io) SELECT FCODE, MAXCPRICED
GARMENT GROUP BY CODE
() SELECT DESCRIPTION, PRICE FROM GARMENT
WHERE GARMENT. FCODE ~ FABRIC. FECOE
AND TYPE = “SILK"
21. Consider the following tables. Write SQL
‘commands for the statements () to (v).
INCPRICE) FROM
SendertD SenvderName SenderAddress _SenderCity
NDOL_ Rain 2,ABC Appts New Delhi
MUO ——-HSinha 12, Newtown Mumbai
MUI5—SJha T/A, Park Stet Mumbai
NDSO——T Prasad ——(LK,SDA_ New Delhi
‘Table: RECIPIENT
ecID SenderID RecName ReeAddress _ RecCity
O05 NDOL —_RBajpayee 5, Central Avenue Kolkata
DOS MUO2 —SMahajan 116,AVihar New Delhi
MUIO NDOL —HSingh 2A, Andheri East Mumbai
MUS2 _MUIS PK Swamy B5,C$ Terminus Mumba
NDS ND5O | STupwthi 13,01 D, New Delhi
Mayur Vihar
(i) To display the names ofall Senders from
Mumbai
(ii) To display the ReclD, SenderName,
SenderAddress, ReeName, RecAddress for every
Recipient
(ii) To display Recipient details in ascending order of
ReeName,
(is) To display number of Recipients from each City.
(9) To display the detail of recipients who are in
Mumba
‘Ane, (i) SELECT Senderane FROM SENDER MHERE SenderCity
= nba
(i) SELECT RecID. SenderNane. SenderAddiress.
Recleme. RecAddress
FROM RECTPIENT. SENDER WHERE
RECIPIENT Sender ~ SENDER. Sender:
(iy SELECT * FROM RECIPIENT ORDER BY Rechane:
(Gv) SELECT COUNTC*) AS “No, of Recipients
RecCity FROM RECIPIENT
GROUP BY RecCity:
CBSE Term Il | Computer Science Xil
(@) SELECT * FROM RECIPIENT
WHERE RecCity = “Runbai”
2B. Write the SQL commands for (i) to (v) on the basis
of the table HOSPITAL.
able : HOSPITAL,
No. Name Age Department Dateofadim Charges Sex
1 Sandeep 65 Surgery sss 300M
2 Ravina 24 Orthopaedic 200188 200 F
3 Karan 45 Orthopaedic 190288 200M
4 Tarn 12 Surgery oes 300M
5 Tubin 96 ENT 0s 350M
6 Ketaki 16 ENT auozes a0
7 Ankia 29 Cardiology 200208 800
8 Zarwen 45 Gynaecology — 2200268 900 F
9 Kush 19 Cardiology 190188 800M
10 Shailya 31 Nuclear 1929s 400M
Medicine
(i) To show all information about the patients of
Cardiology Department
(Gi) To list the name of female patients, who are in
Orthopaedic Department.
(ii) To list names ofall patients with their date of
admission in ascending order:
(iv) To display Patient's Name, Charges, Age for male
patients only.
(9) To display name of doctor are older than 30 years
tnd charges for consultation fee is more than
500,
‘Ana (i) SELECT * FROM HOSPITAL WHERE Departaent. =
Cerdioloay
(ii) SELECT Nane FROM HOSPITAL WHERE Department. =
Orthopaedic’ AND Sex
=F
(ii) SELECT Name FROM HOSPITAL ORDER BY Dateotadm:
(is) SELECT Nane, Charges, Age FROM HOSPITAL WERE
Sex =H
(©) SELECT MRE FROM HOSPITAL WHERE Age>S0 AND
Charges>500:
)/CBSE_TG) (CBSE Term I!| Computer Science Xi
28. Write SQL. commands for(i) t (4) on the basis of
table INTERIORS.
Table : INTERIORS
No. ITEMNAME TYPE DATEOF pr
1 Redros Double Bed 2590202 32000 15,
Softtouch __Babyeot 20012 900010
3 Jemy’shome Baby eot 1902/02 0
4 Rough wood Oice Table 010102 20
5 Comfort zone Double Bed 120102 15000 20
6 Jemylook —Babyeot 24022 70019
Lionking __Oflce Table 200202 1600020
8 Royaltiger Sofi 2202/02 3000025
© Parksiting Sofi 101900015,
10 Dine Paradise Dining Table 190202 11000 15
11 White Wood Double Bed 230500 20000 20
12 James OT Sola 2002/00 15000 15
13 Tomlook ——Babyeot 210208 700010
(i) To show all information about the Sofa from the
INTERIORS table
(ii) To list the ITEMNAME, which are priced at
‘more than 10000 from the INTERIORS table
(il) To list ITEMNAME and TYPE of those items, in
which DATEOFSTOCK is before 22/01/02 from
the INTERIORS table in descending order of.
ITEMNAME,
(iv) To insert a new row in the INTERIORS table
with the following data
(14, “Truetndian’, “Office Table’
25/03/03", 15000, 20}
(0) To display the name of item with their price
which have discount more than 20.
‘Ans, (i) SELECT * FROM INTERIORS WHERE TYPE
= Sofa’
(i) SELECT ITEM FROM INTERIORS MHERE PRICE >
10000:
(Gy SELECT TTEMUWE, TYPE FROM INTERIORS HHERE
DDATEOFSTOCK < *22/01/02
ORDER BY ITEHWAME DESC
(jv) INSERT INTO INTERIORS VALUES (14, "Trueindian’
Office Table’
25/03/03" .15000,20)
() SELECT ITEM, PRICE FROM INTERIORS WRE
DrscoxnT>20:
30. Given the following tables for a database LIBRARY,
‘Table : BOOKS
Book, Author
Book td ROPk- Author Publishers Price Type Qty
0001 The Willa Fist Fubl 750. Fiction 10
Tears Hopkins
0002 Thunder Anna Fist Publ 700 Fletion 5
bolts Roberts
TWOL My —-Brain& EPB 250 Tet 10
Fist Brooke
ct
Tome C++ AW. TDH 25 Test
Brainwo Rosse
ns
COOOL Fast Lata ERB 350 Ghokery 8
Cook Kapoor
able : ISSUED.
Book Id Quantity Issued
F001 3
“TH001 1
‘C000 5
Write SQL queries for (to y)
(0)To show Book name, Author name and Price of
books of EPB Publishers,
(ii) To lst the names from books of Fiction type.
(ii) To display the names and price of the books in
descending order of their price
(iv) To inerease the price ofall books of First Publ
Publishers by 50.
(6) To display the detail of book whose quantity less
than 10,
‘Ans. () SELECT Book Nae. Author Name, Price FROM
BOOKS WERE Publishers = "EPS
(SELECT Book_Name FROM BOOKS WHERE Type =
Fiction
) SELECT Book Nane. Price FROM BOOKS ORDER BY
Price Ese
(Gv) UPDATE 800KS SET Price = Price + 50
WHERE PubTishers = “First Pub?
(©) SELECT *FROM BOOKS
WERE aty<10:
)/CBSE_TG) 31M
QL. commands for (to (6) on the basis of
table STUDENT.
Table : STUDENT
SNO_NAME STREAM FEES AGE SEX
1 ARUNKUMAR COMPUTER 75000 17M
2 _DIVYAJENEJA COMPUTER 75000 18 F
KESHAR, BIOLOGY 50000 16M.
MEHRA
HARISH SINGH ENG.DR 35000 18M.
4
5 PRACH ECONOMICS 30000 19 F
6
NISHAARORA COMPUTER 75000 15
DEEPAK ECONOMIC 30000 16M.
KUMAR s
S sania BIOLOGY 50000. 15 F
VASWANI
(i) To count the number of female students.
(ii) To display the number of students stream wise,
(iv) To display all the records in sorted order of
(0) To display the stream of student whose name is
Harish,
‘Ana, (i) SELECT NMWE FROM STUDENT WHERE: STREN
= CORPUTER
(i) SELECT COUNT(*) FROM STUDENT WHERE SEX = “F
(Gi) SELECT STREAM, COUNT) FROM STLDENT GROUP BY
STREAM
(Gv) SELECT * FROM STUDENT ORDER 8Y NAME
() SELECT STREAM FROM STUDENT
WHERE NAME LIKE “EHARISHY
32. Given the following family relation, Write SQL
‘commands for questions (i) to (v) based on the table
AMI
‘Table FAMILY
No. Name Eee Mle gs Income Occupation
1 Mish 3 27000 Serve
2 Gwa 4 150000 Bins
3 Kin 8 35000 Mined
4 Chadha 2 2250 Bose
3 Yay 7 2 20000 Mud
6 Joshi 3 214000 Service
7 Mauve 6 35000 Farming
5 tao 5 210000 Service
CBSE Term Il | Computer Science Xil
(i) To select all the information of fumily, whose
Occupation is Service.
(Gi) To list the name of family, where female
members are more th
(li) To list all names of family with income in
mug order.
(iv) To count the number of family
Tess than 10000.
(9) To display the detail of family whose income is.
more than 10000 and occupation is mised type.
‘Ans, (i) SELECT * FROM FAMILY WHERE Occupation =
‘Serv
(Gi) SELECT Nane FROM FAMILY KHERE FenaleMenbers >
3
whose income is
(Gi) SELECT Wame, Income FROM FAMILY ORDER BY
Inco:
(jv) SELECT COUNTC*) FROM FAMILY KHERE Income <
30000:
(@) SELECT *FR0M FAMILY
WHERE. INCOME > 10000
AND Occupation = “Hixed”
Zonsider the following tables PRODUCT and
CLIENT. Write SQL commands for the statement
(to (),
33.
‘Table : PRODUC
ProductName Manufacturer Price
‘TROL Tuloom Powder LAK 0
FW05 Face Wash ABC
S01 Bath Soap ABC
SHO6 Shampoo az
FW. Face Wash aw %
Table s CLIENT
CAD ClientName City
OL Cosmetic Shop Delhi FWOS,
06 Toa Health Mumbai_—_-BSOL
2 LivelLife Delhi S105
15 Pretty Woman Delhi FW
16 Dreams Bengaluru TPOL
(i) To display the details of those Clients, whose
City is Delhi
(ii) To display the details of produets, whose Price is
in the range of 50 to 100 (both values included)
(ii) To display the ClientName, City from table
SLIENT and ProduetName and Price from table
PRODUCT, with their corresponding matching
PD.
)/CBSE_TG) (CBSE Term I!| Computer Science Xi
(iv) To increase the Price of all produets by 10.
(v)To display the product detail whose
‘manufacturer is ABC and price less than 50,
‘Ans, (i) SELECT * FROM CLIENT WHERE City="Delhi
(i) SELECT * FROH PRODUCT MHERE Price BETHEEN 60
‘ano oa
(iy SELECT Clienthane. city. Productione, Price
FROM CLIENT, PRODUCT
WHERE CLIENT.P_10 ~ PRODUCT. P_tO:
(i) UPDATE PRODUCT SET Price = Price + 10:
(o) SELECT #7R0H saoucT
WEARE Manufacturer = “ABC
00 Price < 60:
34, Study the following tables DOCTOR and SALARY
and write SQL commands for the questions (i to
WW.
able : DOCTOR
1D NAME DEPT sex Sgrere
101 John ENT. M 2
104 Smith ORTHOPEDIC Mt 3
107 George CARDIOLOGY _M 0
1M Lara SKIN F 3
109K George MEDICINE F 0
105 Johnson ORTHOPEDIC Mt 10
LT Lucy ENT. F 3
Bill MEDICINE F 2
130 Morphy ORTHOPEDIC _M 6
‘Table | SALARY
ID__BASIG_ ALLOWANCE. CONSULTATION
io 1200 1000 300
10123000 2300 500
17330004000 500
14120005200 10
109420001700 200
10518900 1600 300
190217003600 300)
(i) Display NAME ofall doctors who are in
MEDICINE department having more than
LOyrs experience from the table DOCTOR.
ii) Display the average salary of all doctors working
in ENT department using the tables DOCTOR
and SALARY, SALARY = BASIC +
ALLOWANCE.
(ii) Display the minimum ALLOWANCE of female
doctors.
(iv) Display the highest consultation fee among all
ale doctors.
(9) To display the detail of doctor who have
experience more than 12 years,
‘Ang, (i) SELECT NAVE. FROM DOCTOR KHERE DEPT =
MEDICINE” AND EXPERIENCE > 10
(Gi) SELECT AVG(BASIC + ALLOWANCE) FROM SALARY
WHERE SALARY. ID TN(SELECT 1D FROM DOCTOR WHERE
DEPT = “ENT
(ii) SELECT WINCALLOAANCE) FROM SALARY WHERE
SALARY. 1D IN(SELECT 1D FROM DOCTOR WHERE SEX =
F)
(Gv) SELECT MAK(CONSULTATION) FROM SALARY WHERE
SALARY. 1D IN(SELECT ID FROM DOCTOR WHERE SEX
W)
(@) SELECT * FROM oCTOR
HERE: EXPERTENCED12:
35, Study the following tables FLIGHTS and FARES)
and write SQL commands for the questions (i) to
(iv),
‘Table: FLIGHTS
FL_NOSTARTING ENDING __NO_FLIGHT NO_sTOPS
Ic901 MUMBAI DELHI 8 °
1c) BENGALURU ELH 2 1
MCIOL INDORE MUMBAI 3 °
1ca2DELI MUMBAI 5 °
AMSIZ KANPUR BENGALURU 3 1
Ics MUMBAT—_ KOCHE 1 4
AMSOL DELI ‘TRIVANDRU 1 5
M
MUIS9 MUMBAI MADRAS 3 8
ICO DELI AHMEDABAD 4 °
Table: FARES
FLNO AIRLINES FARE TAX
ICM INDIAN 6500 0
AIRLINES:
MUIG9 SAHARA 100
AMBOL JETAIRWAYS 15450
Ics INDIAN ‘00
AIRLINES.
1ca2 INDIAN 4500 w
AIRLINES.
1¢799 INDIAN 10500 w
AIRLINES
MCIOL DECCAN 3500 4
AIRLINES (i) Display FL_NO and NO_PLIGHT from.
KANPUR to BENGALURU from the table
FLIGHTS.
(ii) Arrange the contents ofthe table FLIGHTS in
the ascending order of FL_NO.
(ii) Display the FL_NO and fare to be paid for the
flights from DELHI to MUMBAL using the
tables FLIGHTS and FARES, where the fare to
be paid = FARE + FARE * TAX % 100,
(iv) Display the minimum fare INDIAN AIRLINES
is offering from the table FARES.
(v)"To display the detail fares of Indian airlines,
‘Ans, (i) SELECT FL_NO, NO_FLIGHT FROM FLIGHTS
WHERE STARTING = "KANPUR" AND ENDING =
BENGALURU
i) SELECT * FRON FLIGHTS ORDER BY FL_NO:
(i) SELECT FL_NO, FARE + FARE * TAXRIOO FROM FARES
WHERE FL_HO = (SELECT FL_No FROM FLIGHTS WHERE
STARTING = “OELAI" AND ENDING = “HOMBAI")
(Gv) SELECT MINCFARE) FROM FARES GROUP BY AIRLINES
HAVING AIRLINES = "INDIAN AIRLINES
(6) SELECT * FROM FARES
WHERE ATRLINES = "Indian Air] ines”
36. Write SQL commands for (i to (iv) on the basis of
the table SPORTS
Table : SPORTS
STUD-E ena
STUD-E Ciass NAME GAMEI GRADEGaME GIA
10 Samver Cricket B Swimming A
n Suit Tennis A Skating =
BR Kamal Swimming B Football B.
137 Veena Tennis CG TennlsA
19 Archana Baskethall A Cricket A
15 10 Arpt Cricket’ =A Athletes
() Display the games taken up by the students,
whose name starts with
(ii) Write a query to add a new column named
MARKS.
(iil) Write a query to assign a value 200 for Marks for
all those, who are getting grade ‘B’ or grade ‘8’in
both GAME1 and GAME2.
(iv) Which command will be used to arrange the
whole table in the alphabetical order of NAME?
(a) SELECT FROM SPORTS ORDER BY NAME.
(b) SELECT*SPORTS ORDER BY NAME:
CBSE Term Il | Computer Science Xil
(c) SELECT*FROM SPORTS ORDER NAME:
(a) SELECT#FROM SPORTS ORDER BY NAME:
‘Ans. (i) SELECT GAME, GAME2 FROM SPORTS UHERE NAME
LIKE “as
(G) ALTER TABLE SPORTS ADD(HARKS NUMBER(3))
(Gi) UPDATE. SPORTS SET MARKS ~ 200
WHERE GRADE = “A' OR GRANDE = “8° OR GRADE
‘A OR GRADEL = “8
(iv) (@) SELECT * FROM SPORTS ORDER BY NAME
37. Write SQL commands for i) to (iv) on the basis of
table EMPLOYEE,
Table: EMPLOYEE
Ni) NAME. asic DEranrMENT PATO AGE sex
1 KARAN $000 / PERSONNEL 270897 55M
2 DIVAKARS500 COMPUTER 200198 34M
3 DIVA 7300 ACCOUNTS 10207 34 F
4 ARUN S350 PERSONNEL —OWIA5 33M
5 SABINA 9500 ACCOUNTS 120196 95 F
6 JOHN 7400 FINANCE 24297 36M
7 ROBERT $250 PERSONNEL 200207 39M
8 RUBINA 9450 MAINTENANCE 220208 57 F
9 VIKAS 7500 COMPUTER 13014 41M
10 MOHAN $300 MAINTENANCE 190295 97M
‘command will be used to list the names of
nployees, who are more than 34 years old.
sorted by NAME,
) SELECT NAME FROM EMPLOYEE WHERE AGE>94 ORDER
BY NeME
(b) SELECT * FROM ENPLOYEE WHERE AGE%34 ORDER BY
ame:
(c) SELECT NAME FROM EMPLOYEE MHERE AGE>24
(4) SELECT NAME FROM EMPLOYEE AGE>24 ORDER BY
aM:
(ii) Write a query to display a report, listing NAME,
BASIC, DEPARTMENT and annual salary
Annual salary equals to BASIC * 12,
(ti) Insert the following data in the EMPLOYEE
table
11, ‘VIJAY’, 9300, ‘FINANCE’, 13/7/98', 35, “M"
(iv) Write a query to count the number of employees,
who ate either working in PERSONNEL oF
COMPUTER department,
)/CBSE_TG) (CBSE Term I!| Computer Science Xi
‘Ans, (i) (a) SELECT NAME FROM EMPLOYEE WHERE AGE > 98
ORDER BY NAME
(@) SELECT NAME, BASIC, DEPARTMENT, BASIC*12 “Annus
Selary” FROM EMPLOTEE
(Gy INSERT INTD ERPLOVEE VALUESC LI,
FINANCE", "13/7/98", 35.°H")
(Gv) SELECT COUNT(#) FROM EMPLOYEE
vay", 9300)
WHERE DEPARTHENT = “PERSONNEL” O8 DEPARTHEN
COMPUTER
38. Write SQL commands for () to (iv) on the buss of
table COLLEGE
Table : COLLEGE,
No Name Age Department Datefjoin Base Sox
1 Shale 45 Biology 190285 10500. M
2 Sameera 54 Biology 1001000500 F
3. Youyen 43 Physics ——-27AROS 8500 M
4 Pabush 31 Chemity 11a 500M
5 Aven 51 Matbematis 2240191 500M
6 Recta 27 Chemistry HABA 9000 F
7 _Urashi 29 Biology 10NBBS 8500 F
5 Teoma 35 Mathematis 024289 10500 F
9 Viren 49 Mathematics 050185 008M
10 Prakash 22 Physics 1702028000 M
(i) Write a query to change the Basie salary to
10500 of all those teachers from COLLE
who joined the COLLEGE after 01/02/89 and
are above the age of 50
(ii) Write a query to display Name, Age and Basie
of all those from COLLEGE, who belong to
Physies and Chemistry depart
(iil) Which command will be used to delete a row
from table COLLEGE, in which NAME is
VIREN?
(iv) Insert the following data in the given table
COLLEGE,
11, ‘Saurav’, 50, ‘Chemistry’, "18/05/93",
13000, "Wr
‘Ans, (i) UPDATE COLLEGE SET Basic = 10500
HERE DateofJoin>"01/02/89" AND Age>S0:
(@) SELECT Name, Age. Basic FROM COLLEGE
WHERE Department="Physics’ OR
Department =" Chemistry’
(i) DELETE FROM COLLEGE WHERE Name
VIREN
(Gv) INSERT INTO COLLEGE VALUES (11.
50. “Chemistry”, “18/05/93
12000, “*W")
Saurav Chapter Test
Multiple Choice Questions
1. Riya wants to remove a column "Name" from her table , which command she has to use?
(a) ALTER TABLE tb cuear
(o UPDATE (@) None of these
2. The clause with ALTER TABLE command that renames @ column is
(a) RENAME (b CHANGE
(@ oROP (6) CHANGENAME
13. We can use the aggregate functions in select list or the... clause ofa selec statement. But they cannot be used in @
clause.
(a) WHERE, HAVING (b) GROUP By, HAVING
(HAVING, WHERE (@) GROUP BY, WHERE
“4, Select correct SQL query from below to find the temperature in increasing order ofall cites
ty FROM weather CROER BY temperature:
ELECT city, temperature FROM weather
CT erty. temperature FRIM weather ORDER BY tenperature
y. temperature FRIM weather ORDER
5. The HAVING clause acts lke a WHERE clause, but it identifies columns that meet a criterion, rather than rows,
fa Twe (0 False
(© Depend on query {@ Depend on column
Short Answer Type Questions
6. Explain usage of IS NULL and IS NOT NULL clauses (scent)
"7. With respect to the following table structure write queries forthe following
GamelD._GName Type Players
(0 To display the details of games of "OUTDOOR" type.
(i) To display GName and Players for games where players is more than 2.
£8. Explain working of AND and OR operators in queries
9. Write a query to display the Sum, Average, Highest and Lowest marks of the students grouped by subject and
sub-grouped by cass.
10. the following query is producing an error. identify the error and ako wit the cortect query
T ¥ FROM EMP ORDER BY NAME WHERE SALARY>=5
Long Answer Type Questions
Th With respect to the folloing table “BOOK” write SQL. commands for the questions () 0.
Table : Book
BookiD name Publisher Price _—_—DtofPub
8 Ficion TH 1200 _-2020-09-08,
82 Stories Pa soo NULL
83 Ramayana Pa Nu
24 Beginners Oswal 1990-2-03
Cookng
(0 Display details of books published before year 2000.
(i) Display names and publishers of books whose price is less than 1000.
i) Display names of books who do not have a date of publication
(iu) Increase price ofall books by 200. (CBSE Term II | Computer Science Xil
12. write SOL commands with respect to the Employee table given below.
Table : Employee
Eno Ename Dept__Desig__Dtofloin Salary
1 Jack Sales “MGR 2012.09.12 69000,
2 Priya Accs MGR 2005-04-22 56000
3 RinPers Clerk 2000-01-09 25000
4 Ant Pers Offeer 1994.04.03 67000
5 Sumit Sales Offeer——-NULL_——_—19000,
6 Akash Soles Offeer NULL 20000
{0 Display name and department of employees whose name begins with”
(i Display details of employees whose designation ends with“.
Gi) Display details of employees whose name has Ist letter °P* 3rd letter“
(iv) Display name,deptartment and salary of employees whose department name ends with 's.
‘TB. Consider the following table GARMENT, write SQL commands forthe statements () to (). [CBSE Question Bank 2021)
‘Table : GARMENT
GCODE DESCRIPTION PRICE FCODE READYDATE
10023 PENCIL SKIRT 150 fos 10EC-08
ooo FORMAL SHIRT 1250 ra ANB
“0012 INFORMAL SHIRT 150 2 061UN-06
‘oat BABY TOP 750 Fos or-apr-o7
‘oso TUUP SKIRT 50 ro SrMAR-OT
voo1s EVENING GOWN 880 03 061UN-06
"0009 INFORMAL PANT 1500 2 20-OCT-08
10007 FORMAL PANT 1350 fo 09-Mar-06
ozo FROCK 50 fos 09-SEP-07
o0e9 SLACKS 750 03 20-0CT-08
(0 To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE.
(To cispay the details of al the GARMENT, which have READYDATE in between 08-DEC-07 and 16-JUN-0B (inclusive if both
the dates)
i) To display the average PRICE of all the GARMENT, which are made up of fabric with FCODE as F03,
(iu To splay fabric wise highest and lowest price of GARMENT from GARMENT table. (Display FCODE of each GARMENT
‘along with highest and lowest price),
(To display GCODE whose PRICE is more than 1000,
Answers For Detailed Solutions
Multiple Choice Questions Scan the code
1@ 20 30) 40) 5.0)
)/CBSE_TG)
