Class 12 Computer Science Chapter 6: Database & SQL
• 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 |
Table : Movie
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;
4. Suppose your school management has decided to conduct cricket matches between students of Class XI and Class XII. Students of each class are asked to join any one of the four teams – Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:
a) Create a database “Sports”.
Answer : CREATE DATABASE Sports;
b) Create a table “TEAM” with following considerations:
i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
ii) Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
Answer : CREATE TABLE TEAM (TeamID INTEGER CHECK (TeamID BETWEEN 1 AND 9), TeamName VARCHAR(30) CHECK(Length (TeamName) >= 10) )
c) Using table level constraint, make TeamID as the primary key.
Answer : CREATE TABLE TEAM (TeamID INTEGER CHECK (TeamID BETWEEN 1 AND 9), TeamName VARCHAR(30) CHECK(Length (TeamName) >= 10), PRIMARY KEY(TeamID) );
d) Show the structure of the table TEAM using a SQL statement.
Answer : DESC TEAM;
e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 3: (4, Team Hurricane)
Answer : INSERT INTO TEAM VALUES (1, ‘Team Titan’);
INSERT INTO TEAM VALUES (2, ‘Team Rockers’);
INSERT INTO TEAM VALUES (1, ‘Team Magnet’);
INSERT INTO TEAM VALUES (1, ‘Team Hurricane’);
f) Show the contents of the table TEAM using a DML statement.
Answer : SELECT * FROM TEAM;
g) Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.
Table: MATCH_DETAILS
| MatchID | MatchDate | FirstTeamID | SecondTeamID | FirstTeamScore | SecondTeamScore |
| M1 | 2018-07-17 | 1 | 2 | 90 | 86 |
| M2 | 2018-07-18 | 3 | 4 | 45 | 48 |
| M3 | 2018-07-19 | 1 | 3 | 78 | 56 |
| M4 | 2018-07-19 | 2 | 4 | 56 | 67 |
| M5 | 2018-07-18 | 1 | 4 | 32 | 87 |
| M6 | 2018-07-17 | 2 | 3 | 67 | 51 |
Table: MATCH_DETAILS
Answer : CREATE TABLE MATCH_DETAILS (
MatchId CHAR(2) NOT NULL PRIMARY KEY,
MatchDate DATE,
FirstTeamID INTEGER REFERENCES Team( TeamID),
SecondTeamID INTEGER REFERENCES Team(TeamID),
FirstTeamScore INTEGER,
SecondTeamScore INTEGER
);
5. Using the sports database containing two relations (TEAM, MATCH_DETAILS) and Write the queries for the following:
a) Display the MatchID of all those matches where both the teams have scored more than 70.
Answer : SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
b) Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
Answer : SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore < 70 AND SecondTeamScore < 70;
c) Display the MatchID and date of matches played by Team 1 and won by it.
Answer : SELECT MatchID, MatchDate FROM MATCH_DETAILS WHERE (FirstTeamId = 1 AND FirstTeamScore > SecondTeamScore) OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore) ;
d) Display the MatchID of matches played by Team 2 and not won by it.
Answer : SELECT MatchID FROM MATCH_DETAILS WHERE SecondTeamScore < FirstTeamScore AND (FirstTeamID = 2 OR SecondTeamID = 2) ;
e) Change the name of the relation TEAM to T_DATA. Also change the attributes TeamID and TeamName to T_ID and T_NAME respectively.
Answer : ALTER TABLE TEAM RENAME TO T_DATA;
ALTER TABLE T_DATA CHANGE TeamID T_ID INTEGER;
ALTER TABLE T_DATA CHANGE TeamName T_Name VARCHAR(30) ;
6. A shop called Wonderful Garments who sells school uniforms maintains a database SCHOOLUNIFORM as shown below.
It consisted of two relations – UNIFORM and COST. They made UniformCode as the primary key for UNIFORM relations. Further, they used UniformCode and Size to be composite keys for COST relation. By analyzing the database schema and database state, specify SQL queries to rectify the following anomalies.
a) M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each.
b) INSERT INTO COST (UCode, Size, Price) values (7, ‘M’,100);
When the above query is used to insert data, the values for the handkerchief without entering its details in the UNIFORM relation is entered. Make a provision so that the data can be entered in the COST table only if it is already there in the UNIFORM table.
Answer : To overcome this anomalies, you need to create a foreign key constraint in cost table. Here UCode of cost table get related with the UniformCode Primary Key of Uniform table.
ALTER TABLE COST
ADD FOREIGN KEY (UCode) REFERENCES UNIFORM(UniformCode);
c) Further, they should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraints to the SCHOOLUNIFORM database.
Answer : To overcome this this problem, you need to specify NOT NULL Constaint to UName column of UNIFORM table. So that you always suppose to enter valid name to uniform, while adding a record in a table.
ALTER TABLE UNIFORM
MODIFY UName VARCHAR(30) NOT NULL;
d) Add the constraint so that the price of an item is always greater than zero.
Answer : Add a CHECK constraint on price filed of COST table.
ALTER TABLE COST
ADD CONSTRAINT CHECK(Price > 0);
7. Consider the following table named “Product”, showing details of products being sold in a grocery shop.
Write SQL queries for the following:
a) Create the table Product with appropriate data types and constraints.
b) Identify the primary key in Product.
Answer: PCode
c) List the Product Code, Product name and price in descending order of their product name. If PName is the same then display the data in ascending order of price.
Answer: SELECT PCODE, PNAME, UPRICE
FROM PRODUCT
ORDER BY PNAME DESC, UPRICE ASC;
d) Add a new column Discount to the table Product.
Answer: ALTER TABLE PRODUCT ADD DISCOUNT DECIMAL (10,2);
e) Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0.
Answer:
mysql> UPDATE PRODUCT SET DISCOUNT = UPRICE * 0.10
WHERE UPRICE > 100;
f) Increase the price by 12 per cent for all the products manufactured by Dove.
Answer:
mysql> UPDATE PRODUCT
SET UPRICE = UPRICE + UPRICE * 0.12
WHERE MANUFACTURER = ‘DOVE’;
g) Display the total number of products manufactured by each manufacturer.
Answer:
mysql > SELECT MANUFACTURER, COUNT(*)
-> FROM PRODUCT
-> GROUP BY MANUFACTURER;
Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
h. SELECT PName, Avg(UPrice) FROM Product GROUP BY Pname;
i. SELECT DISTINCT Manufacturer FROM Product;
j. SELECT COUNT(DISTINCT PName) FROM Product;
k. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
a) Add a new column Discount in the INVENTORY table.
Answer: ALTER TABLE INVENTORY ADD DISCOUNT DECIMAL(10,2);
b) Set appropriate discount values for all cars keeping in mind the following:
(i) No discount is available on the LXI model.
(ii) VXI model gives a 10% discount.
(iii) A 12% discount is given on cars other than LXI model and VXI model.
Answer: (I) UPDATE INVENTORY SET DISCOUNT = NULL WHERE MODEL = ‘LXI’;
(II) UPDATE INVENTORY SET DISCOUNT = PRICE * 0.10 WHERE MODEL = ‘VXI’;
(II) UPDATE INVENTORY SET DISCOUNT = PRICE * 0.12 WHERE MODEL NOT IN (‘LXI’, ‘VXI’)
c) Display the name of the costliest car with fuel type “Petrol”.
Answer: SELECT CARNAME FROM INVENTORY WHERE PRICE = (SELECT MAX(PRICE) FROM INVENTORY WHERE FUELTYPE = ‘PETROL’ );
d) Calculate the average discount and total discount available on Car4.
Answer: SELECT AVG(DISCOUNT), SUM(DISCOUNT) FROM INVENTORY GROUP BY CARNAME HAVING CARNAME = ‘CAR4’;
e) List the total number of cars having no discount.
Answer: SELECT * FROM INVENTORY
WHERE DISCOUNT IS NULL;
Discover more from EduGrown School
Subscribe to get the latest posts sent to your email.