Computer Science – XII Chapter 9

Chapter 9
Structured Query Language

Structured Query Language (SQL) is a language designed for managing data in RDBMS. It
provides facilities to create a table, insert data into a table, retrieve information from a
table, modify data in the table, delete the existing data from a table, modify the structure of
a table, remove a table from a database, etc.

Components of SQL: Data Definition Language (DDL), Data Manipulation language (DML)
and Data Control Language (DCL).
DDL commands are used to create, modify and remove the database objects such as tables,
views and keys. Eg: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE VIEW, DROP VIEW.
DML permits users to insert data into tables, retrieve existing data, delete data from tables
and modify the stored data. Eg: INSERT INTO, SELECT, UPDATE, DELETE FROM.
DCL includes commands that control a database, including administering privileges and
committing data. Eg: GRANT, REVOKE.

SQL Data Types: INT or INTEGER, DEC or DECIMAL, CHAR or CHARACTER, VARCHAR, DATE, TIME.
DEC(5,2) or DECIMAL(5,2) denotes that the column with this specification can store any value
having a maximum of five digits, out of which two are after the decimal point.
CHAR is a fixed length character data type. It is mainly used when the data in a column are of
the same fixed length and small in size. VARCHAR represents variable length strings. The
space allocated for the data depends only on the actual size of the string, not on the
declared size of the column.

SQL Commands
Command
Syntax Optional Purpose
and use
CREATE TABLE CREATE TABLE tbl_name Constraints – The
(To create a (col_name data type <constraint>, rules enforced on
table) col_name data type <constraint>, data that are
……………………… entered into the
col_name data type <constraint>); column of a table. 1. To uniquely identify a
1. PRIMARY KEY row of a table.
2. AUTO_INCREMENT 2. To assign serial
3. NOT NULL numbers automatically.
4. UNIQUE 3. To avoid null value.
4. To avoid duplication.
5. DEFAULT
5. To set a default value.
ALTER TABLE ALTER TABLE tbl_name DROP column Instead of ADD / MODIFY
(To change ADD / MODIFY use DROP to remove an
the structure col_name data type <constraint>; existing column.
of a table) (To add a new column or modify RENAME TO To change the name of a
an existing column in a table) new_tbl_name; table.

1
Joy John’s CS capsules
Computer Science – XII Chapter 9

DROP TABLE DROP TABLE <table_name>; To remove a table from a

database.
INSERT INTO INSERT INTO tbl_name Use of Null as a If a row does not contain
(To insert a VALUES (val1, val2, val3, ... ); value values for all the
record) columns, the keyword
Null should be given for
the respective column.
SELECT SELECT col_name1, col_name2, … DISTINCT column To avoid duplicate values
FROM tbl_name; in the given column while
selecting rows.
(To retrieve (Instead of specifying all columns, WHERE condition; To select only those rows
information the symbol * can be used) (Conditions are which satisfy the given
from table) made using condition.
relational operators)
ORDER BY column To list the selected rows
(Keyword DESC in ascending order of
may be used after values in the specified
column name to column.
get the list in
descending order)
GROUP BY column To group the rows having
(Usually used same value in the
when aggregate specified column. The
function are SELECT command will be
applied) applied on the groups.
HAVING condition To form groups based on
(Used with condition.
GROUP BY clause)
UPDATE UPDATE tbl_name WHERE condition; To modify the columns of
(To modify SET col_name = value; only those rows which
the values in (Value may be a constant or an satisfy the specified
columns) expression) condition.
DELETE DELETE FROM tbl_name; WHERE condition; To delete only those rows
(To delete which satisfy the
rows/records) specified condition.

Additional Commands
Command Purpose
CREATE DATABASE db_name; To create a new database.
USE db_name; To open a database to perform operation on tables.
DESCRIBE tbl_name; To display the structure of a table.
SHOW TABLES; To list the tables in the current database.

2
Joy John’s CS capsules
Computer Science – XII Chapter 9

Relational Operators for setting conditions

< <= > >= = <> (Not equal to)
Special Operators & Aggregate Functions
Operators are used to make conditions to attach with WHERE clause.
Functions used with SELECT command to get processed results from tuples.
Operator/
Purpose Example
Function
LIKE To identify pattern matching. SELECT * FROM Student
WHERE Name LIKE “%Kumar”;
BETWEEN To identify value that falls in a SELECT * FROM Employee
... AND given range. WHERE Salary BETWEEN 10000 AND 20000;
IN To identify value from a given SELECT * FROM Bank
list. WHERE Branch IN (“Trivandrum”,
“Ernakulam”, “Kozhikode”);
IS To identify null values in a SELECT * FROM Stock
column. WHERE Tax IS NULL;
AND To select rows when two or SELECT * FROM Student
more conditions are TRUE. WHERE Batch = “Science” AND Marks > 50;
OR To select rows when any one of SELECT * FROM Players
the conditions is TRUE. WHERE Game = “Cricket” OR Game = “Hockey”;
NOT To select rows when the given SELECT * FROM Stock
condition is FALSE. WHERE Tax IS NOT NULL;
Aggregate Functions
COUNT() To count the non-null values of a column. SELECT COUNT(Fee) FROM Student;
Also used to get number of rows SELECT COUNT(*) FROM Student;
SUM() To find the sum of values in a column. SELECT SUM(Fee) FROM Student;
AVG() To find the average of values in a column. SELECT AVG(Salary) FROM Employee;
MAX() To find the highest value in a column. SELECT MAX(Marks) FROM Student;
MIN() To find the lowest value in a column. SELECT MIN(Marks) FROM Student;

Nested Query
When we use SELECT command with WHERE clause, the condition may be framed with
another SELECT query.
For example, the following statement gives the details of students who got highest marks:
SELECT * FROM Student
WHERE Marks = (SELECT MAX(Marks) FROM Student);

The following statement gives the details of employees who get lowest salary:
SELECT * FROM Employee
WHERE Salary = (SELECT MIN(Salary) FROM Employee);
View: It is a virtual table that does not really exist in the database, but is derived from one
or more tables. A view can be created with the DDL command CREATE VIEW.
CREATE VIEW <view_name>
AS SELECT <columns> FROM <table_name>

3
Joy John’s CS capsules
Computer Science – XII Chapter 9

[WHERE <condition>];

To remove a view definition, use the command DROP VIEW <view_name>;

Questions from Previous Years’ Question Papers (Computer Science)
1. Give the correct syntax of the queries in SQL for the following:
(a) Renaming a table
(b) Deleting rows from a table.
(c) Changing definition of a column.
(d) Removing columns from a table.
(e) Adding a new column. (5) (March 2016)
2. What happens when we use DELETE FROM command without a WHERE clause?
(1) (SAY 2016)
3. If a table named “mark” has fields regNo, subCode, and marks, write SQL statements for
the following:
(a) List the subject codes eliminating duplicates.
(b) List the marks obtained by students with subject codes 3001 and 3002.
(c) Arrange the table based on marks for each subject.
(d) List all the students who have obtained marks above 90 for the subject codes 3001
and 3002.
(e) List the contents of the table in the descending order of marks. (5) (SAY 2016)
4. Distinguish between DDL and DML and give examples for each type. (5) (March 2017)
5. Null values in tables are specified as “null”. State whether true or false.(1) (March 2017)
6. Which command is used to delete the table?
(a) delete from (b) drop table (c) delete table (d) drop view (1) (SAY 2017)
7. Differentiate between CHAR and VARCHAR data types in SQL. (3) (SAY 2017)
8. Name the most appropriate SQL data types required to store the following data:
(a) Name of a student (maximum 70 characters)
(b) Date of Birth of a student
(c) Percent of marks obtained (correct to 2 decimal places) (3) (SAY 2017)

Questions from Previous Years’ Question Papers (Computer Applications)

1. _____ keyword is used in SELECT query to eliminate duplicate values in a column.
(a) UNIQUE (b) DISTINCT (c) NOT NULL (d) PRIMARY (1) (March 2016)
2. Consider the following table named ACCOUNTS:
Acc. No. Name Branch Amount
1001 Anil Trivandrum 30000
1002 Sanjay Ernakulam 130000
1003 Meera Kottayam 275000
1004 Sneha Kottayam 50000
1005 Rajan Thrissur 75000
(a) Write SQL statements to do the following:
(i) Display all the details of accounts with amount greater than 50000 in Ernakulam
branch.

4
Joy John’s CS capsules
Computer Science – XII Chapter 9

(ii) Display Acc. No., Branch and Amount in the descending order of amount.
(iii) Display the number of accounts in each branch. (3) (March 2016)
(b) Write SQL statements to do the following:
(i) Add a new record into the table.
(ii) Update the amount of Sanjay to 100000.
(iii) Delete the details of Anil. (3) (March 2016)
3. How will you add a new column to an existing table using SQL statement?
(2) (March 2016)
4. ____ clause of SELECT query is used to apply condition to form group of records.
(a) order by (b) group by (c) having (d) where (1) (SAY 2016)
5. What is a view? How can we create a view using SQL statement? (2) (SAY 2016)
6. (a) Explain the SQL statements used to insert and delete data from a table.
(3) (SAY 2016)
(b) Explain any two DDL commands. (3) (SAY 2016)
7. ____ is an SQL data type which is used to represent variable length string.
(1) (March 2017)
8. The structure of the table ‘EMPLOYEE’ is given below:
Empcode Numeric
Empname String
Basicpay Numeric
DA Numeric
Grosspay Numeric
Write SQL statements for the following:
(a) Insert a record into the table.
(b) Update DA with 60% basic pay.
(c) Display the details of employees whose basic pay is greater than 20000.
(d) Rename the table EMPLOYEE to EMPDETAILS. (5) (March 2017)
9. ____ command in SQL is used to display the structure of a table.
(a) LIST (b) STRUCT (c) DESCRIBE (d) SHOW (1) (SAY 2017)
10. Explain primary key constraint with an example. (2) (SAY 2017)
11. Write SQL for:
(a) Create a table student with the data [name char(20), rollno number(3), marks
number(3)].
(b) List name and rollno of all students.
(c) List name and rollno of students having marks > 600. (3) (SAY 2017)
12. An employee table contains name, empno, basicpay, design.
Write SQL for
(a) Display name, empno and basicpay of all managers. (design = “manager”)
(b) Display empno and salary of all employees.
(salary = basicpay + da) (da = basicapy * 1.15)
(c) Display name and empno of all the employees whose basicpay < 10000.
(3) (SAY 2017)

5
Joy John’s CS capsules