Class 12 Computer Science
Ch-9 Structured Query Language (SQL)
NCERT Book Exercise Solution
A Quick Review
• Database is a collection of related tables. MySQL is a ‘relational’ DBMS.
• DDL (Data Definition Language) includes SQL statements such as, Create table, Alter table and Drop table.
• DML (Data Manipulation Language) includes SQL statements such as, insert, select, update and delete.
• A table is a collection of rows and columns, where each row is a record and columns describe the feature of records.
• ALTER TABLE statement is used to make changes in the structure of a table like adding, removing or changing datatype of column(s).
• UPDATE statement is used to modify existing data in a table.
• WHERE clause in SQL query is used to enforce condition(s).
• DISTINCT clause is used to eliminate repetition and display the values only once.
• The BETWEEN operator defines the range of values inclusive of boundary values.
• The IN operator selects values that match any value in the given list of values.
• NULL values can be tested using IS NULL and IS NOT NULL.
• ORDER BY clause is used to display the result of a SQL query in ascending or descending order with respect to specified attribute values. By default the order is ascending.
• LIKE operator is used for pattern matching. % and _ are two wild card characters. The per cent (%) symbol is used to represent zero or more characters. The underscore (_) symbol is used to represent a single character.
• A Function is used to perform a particular task and return a value as a result.
• Single Row functions work on a single row of the table and return a single value.
• Multiple Row functions work on a set of records as a whole and return a single value. Examples include COUNT, MAX, MIN, AVG and SUM.
• GROUP BY function is used to group rows of a table that contain the same values in a specified column.
• Join is an operation which is used to combine rows from two or more tables based on one or more common fields between them.
1. Answer the following questions:
a) Define RDBMS. Name any two RDBMS software.
Answer: RDBMS stands for Relational Database Management System. An RDBMS is a DBMS designed especially for relational databases, which provide the facility to store and manage large amount of data. It allows to store the data in structured format using rows and columns.
Two RDBMS Software are – MySQL, Oracle
b) What is the purpose of the following clauses in a select statement?
i) ORDER BY
ii) GROUP BY
Answer (i) ORDER BY – ORDER BY clause is used with SELECT statement, to arranges the result of an SQL query in either ascending or descending on the basis of particular columns (fields).
(ii) GROUP BY – The GROUP BY clause is used in SELECT statement, to divide the table into groups i.e combines all records that have identical values in a particular field.
Grouping can be done by a common column name or with aggregate functions in which case the aggregate produces a value for each.
For example :
mysql> SELECT GENDER, COUNT(*) FROM STUDENT GROUP BY GENDER;
OUTPUT:
GENDER COUNT(*)
MALE 20
FEMALE 15
c) Site any two differences between Single-row functions and Aggregate functions.
Answer : Single Row Functions and Aggregate Functions
- Single Row Function applied on the each row while Aggregate Functions applied on the group of rows.
- Single Row Functions return multiple output i.e. output based on each row while Aggregate function returns only one result i.e. output based on group of rows.
d) What do you understand by Cartesian Product?
Answer : A Cartesian product combines the tuples of one relation with all the tuples of the other relation. It is created when two tables are joined without any join condition.
e) Differentiate between the following statements :
i) ALTER and UPDATE ii) DELETE and DROP
i) ALTER and UPDATE
Answer : The ALTER TABLE is a DDL command used to make changes in the structure of a table, such as, adding or deleting a column, modifying the definition of a column, adding / deleting / modifying constraints. It can also used to RENAME the table.
The UPDATE is a DML command used to modify the data stored in a table, such as modifying records or updating records.
ii) DELETE and DROP
Answer : DELETE is a DML command, which is used to remove tuples / records from a table. Delete command will delete only records not a table.
DROP is a DDL command, which is used to remove table, database and views. It deletes entire table including records.
If DROP is used with ALTER TABLE command then it is use to delete/remove column, constraint.
f) Write the name of the functions to perform the following operations:
i) To display the day like “Monday”, “Tuesday”, from the date when India got independence.
Answer : DAYNAME( )
ii) To display the specified number of characters from a particular position of the given string.
Answer : MID( ) or SUBSTRING( )
iii) To display the name of the month in which you were born.
Answer : MONTHNAME( )
iv) To display your name in capital letters.
Answer : UCASE( ) or UPPER( )
2. Write the output produced by the following SQL commands:
a) SELECT POW(2,3);
b) SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);
c) SELECT LENGTH(“Informatics Practices”);
d) SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
e) SELECT LEFT(“INDIA”,3), RIGHT (“Computer Science”,4);
f) SELECT MID(“Informatics”,3,4), SUBSTR(“Practices”,3);
3. Consider the following MOVIE table and write the SQL queries based on it.
| MovieID | MovieName | Category | ReleaseDate | ProductionCost | BusinessCost |
| 001 | Hindi_Movie | Musical | 2018-04-23 | 124500 | 130000 |
| 002 | Tamil_Movie | Action | 2016-05-17 | 112000 | 118000 |
| 003 | English_Movie | Horror | 2017-08-06 | 245000 | 360000 |
| 004 | Bengali_Movie | Adventure | 2017-01-04 | 72000 | 100000 |
| 005 | Telugu_Movie | Action | NULL | 100000 | NULL |
| 006 | Punjabi_Movie | Comedy | NULL | 30500 | NULL |
a) Display all the information from the Movie table.
Answer : SELECT * FROM MOVIE;
b) List business done by the movies showing only MovieID, MovieName and Total_Earning. Total_Earning to be calculated as the sum of ProductionCost and BusinessCost.
Answer : SELECT MovieID, MovieName, ProductionCost + BusinessCost as “Total_Earning” FROM MOVIE;
c) List the different categories of movies.
Answer : SELECT DISTINCT Category FROM MOVIE;
OR
SELECT DISTINCT(Category) FROM MOVIE;
d) Find the net profit of each movie showing its MovieID, MovieName and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.
Answer : SELECT MovieID, MovieName, BusinessCost – ProductionCost as “Net Profict” FROM MOVIE;
e) List MovieID, MovieName and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
Answer : SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE ProductionCost > 10000 and ProductionCost < 100000;
f) List details of all movies which fall in the category of comedy or action.
Answer : SELECT * FROM MOVIE WHERE Category IN (‘Comedy’, ‘Action’);
g) List details of all movies which have not been released yet.
Answer : SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;
