1. Match the following clauses with their respective functions.
[Link] (a).Insert the values in a table
[Link] (b).Restrictions on columns
3. DELETE (c).Table definition
[Link] INTO (d).Change the name of a column
[Link] (e).Update existing information in a table
[Link] (f).Delete an existing row from a table
[Link] (g).Create a database
Answer.
1) (d).Change the name of a column
2) (e).Update existing information in a table
3) (f).Delete an existing row from a table
4) (a).Insert the values in a table
5) (b).Restrictions on columns
6) (c).Table definition
7) (g).Create a database
2. Choose appropriate answer with respect to the following code snippet.
CREATE TABLE student (
name CHAR(30),
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id) );
a) What will be the degree of student table?
i) 30
ii) 1
iii) 3
iv) 4
Answer.
(iii) is correct
As the total no. of attributes in ‘Student’ table are 3 i.e. name, student_is and gender.
So total no. of attributes are 3 and hence the degree is 3.
b) What does ‘name’ represent in the above code snippet?i) a table
ii) a row
iii) a column
iv) a database
Answer.
iii) is correct
As a column in a table represents its attributes and ‘name’ is an attribute, therefore option (iii) is a
correct option.
c) What is true about the following SQL statement?
Select * FROM student;
i) Displays contents of table ‘student’
ii) Displays column names and contents of table ‘student’
iii) Results in error as improper case has been used
iv) Displays only the column names of table ‘student’
Answer.
(ii) is correct
As the given query will show both the column name and its table of content, therefore option (ii) is
correct,
d) What will be the output of following query?
INSERT INTO student
VALUES (“Suhana”,109,’F’),
VALUES (“Rivaan”,102,’M’),
VALUES (“Atharv”,103,’M’),
VALUES (“Rishika”,105,’F’),
VALUES (“Garvit”,104,’M’),
VALUES (“Shaurya”,109,’M’);
i) Error
ii) No Error
iii) Depends on compiler
iv) Successful completion of the query
Answer.
(i) is correct
The given query will give an error as the correct query is as follow:
INSERT INTO student VALUES(“Suhana”,109,’F’),
(“Rivaan”,102,’M’),
(“Atharv”,103,’M’),
(“Rishika”,105,’F’),
(“Garvit”,104,’M’),
(“Shaurya”,109,’M’);
e) In the following query how many rows will be deleted?
DELETE student
WHERE student_id=109;
i) 1 row
ii) All the rows where student ID is equal to 109
iii) No row will be deleted
iv) 2 rows
Answer.
(i) is correct
Only one row will be deleted as ‘student_id’ is a primary key, therefore all row would be having
unique value, therefore only 1 row can have ‘student_id’ as 109.
3. Fill in the blanks:
a) declares that an index in one table is
related to that in another table.
i) Primary Key
ii) Foreign Key
iii) Composite Key
iv) Secondary Key
Answer.
(ii) is correct
Foreign key is the primary key that an index in one table is related to that in another table.
b) The symbol Asterisk (*) in a select query retrieves____________.
i) All data from the table
ii) Data of primary key only
Answer.
(i) is correct
Asterisk (*) is used to display all the data from the table.
[Link] the following MOVIE database and answer 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 - 100000 -
006 Punjabi_Movie Comedy - 30500 -
a) Retrieve movies information without mentioning their column names.
Answer.
SELECT * FROM MOVIE;
b) List business done by the movies showing only MovieID, MovieName and BusinessCost.
Answer.
SELECT MovieID, MovieName, BusinessCost FROM MOVIE;
c) List the different categories of movies.
Answer.
SELECT DISTINCT Category from MOVIE;
d) Find the net profit of each movie showing its ID, Name and Net Profit.
Answer.
SELECT ID, Name, (BusinessCost-ProductionCost) as Net Profit from MOVIE
where ReleaseDate is not NULL;
No, Net Profit is not part of the table ‘MOVIE’.
the Column will be named as ‘Net Profit’.
Profit of unreleased films will not be displayed as the ‘WHERE’ query has been used to handle the
unreleased films.
There will be no row for the unreleased films.
(Hint: Net Profit = BusinessCost – ProductionCost) Make sure that the new column name is
labelled as
NetProfit. Is this column now a part of the MOVIE relation. If no, then what name is coined for
such
columns? What can you say about the profit of a movie which has not yet released? Does your
query
result show profit as zero?
e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID,
Name
and ProductionCost.
Answer.
Select ID, MovieName, ProductionCost where ProductionCost>80000 AND
ProductionCost<125000;
f) List all movies which fall in the category of Comedy or Action.
Answer.
SELECT MovieName from MOVIE where Category=’Comedy’ OR Category=’Action’;
g) List the movies which have not been released yet.
Answer.
SELECT MovieName from MOVIE where ReleaseDate is NULL;
5. 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 int , TeamName char(10) ) ;
c) Using table level constraint, make TeamID as primary key.
Answer.
Alter table Team modify column TeamID int Primary key ;
d) Show the structure of the table TEAM using SQL command.
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 4: (4, Team Hurricane)
Answer.
Insert into Team values (1, Team Titan) ;
Insert into Team values (2, Team Rockers) ;
Insert into Team values (3, Team Magnet) ;
Insert into Team values (4, Team Hurricane) ;
f)Show the contents of the table TEAM.
Answer.
Select * from Team ;
g) Now create another table below. MATCH_DETAILS and insert data as shown in table.
Choose appropriate domains and constraints for each attribute.
Answer.
Create table MATCH_DETAILS ( MatchID char(2) ,
MatchDate date ,
FirstTeamID int,
SecondTeamID int ,
FirstTeamScore int ,
SecondTeamScore int ) ;
insert into Match_details values( “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-20 , 1 , 4 , 32, 87 ) ,
( “M6”, 2018-07-21 , 2 , 3 , 67, 51 ) ;
h) Use the foreign key constraint in the MATCH_DETAILS table with reference to TEAM
table so that MATCH_DETAILS table records score of teams existing in the TEAM table
only.
Answer.
ALTER TABLE Match_details
ADD FOREIGN KEY ( FirstTeamID ) REFERENCES Team( TeamID );
ALTER TABLE Match_details
ADD FOREIGN KEY ( SecondTeamID ) REFERENCES Team( TeamID )
6. Using the sports database containing two relations (TEAM, MATCH_DETAILS), answer
the following relational algebra queries.
a) Retrieve the MatchID of all those matches where both the teams have scored > 70.
Answer.
Select MatchIdD from MATCH_DETAILS where FirstTeamScore > 70 and
SecondTeamScore > 70 ;
b) Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but
SecondTeam has scored > 70.
Answer.
Select MatchIdD from MATCH_DETAILS where FirstTeamScore < 70 and
SecondTeamScore > 70 ;
c) Find out the MatchID and date of matches played by Team 1 and won by it.
Answer.
Select MatchIdD , MatchDate from MATCH_DETAILS where FirstTeamID = 1
and FirstTeamScore > SecondTeamScore ;
d) Find out the MatchID of matches played by Team 2 and not won by it.
Answer.
Select MatchIdD , MatchDate from MATCH_DETAILS
where FirstTeamID = 2 and FirstTeamScore < SecondTeamScore ;
e) In the TEAM relation, change the name of the relation 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 COLUMN TeamID T_ID int ;
ALTER TABLE T_DATA CHANGE COLUMN TeamName T_Name ;
7. Differentiate between the following commands:
a) ALTER and UPDATE
Answer.
ALTER :-
i) It is used to change/update the Structure of table.
ii) It is DDL Command.
iii) Example :- Alter table Student add column Name char(40);
UPDATE :-
i) It is used to update/modify the data or records of a table.
ii) It is DML Command.
iii) Example :- Update Student set Class = 12 ;
b) DELETE and DROP
Answer.
DELETE :-
i) It is used to delete data of table only.
ii) It is DML Command.
iii) Example :- Delete from Student ;
DROP :-
i) It is used to delete database or table from database
ii) It is DDL Command.
iii) Example :- Drop Table Student.
8. Create a database called STUDENT_PROJECT having the following tables. Choose
appropriate data type and apply necessary constraints.
Table: STUDENT
RollNo Name Stream Section RegistrationID
* The values in Stream column can be either Science, Commerce, or Humanities.
* The values in Section column can be either I or II.
Table: PROJECT_ASSIGNED
RegistrationID ProjectID AssignDate
Table: PROJECT
ProjectID ProjectName SubmissionDate TeamSize GuideTeacher
(a) Populate these tables with appropriate data.
(b) Write SQL queries for the following.
(c) Find the names of students in Science Stream.
(d) What will be the primary keys of the three tables?
(e) What are the foreign keys of the three relations?
(f) Finds names of all the students studying in class ‘Commerce stream’ and are guided
by same teacher, even if they are assigned different projects.
Answer.
Create Database STUDENT_PROJECT ;
Create table STUDENT ( RollNo int primary key ,Name char(50),Stream char(10) , Section
char(1) , RegistrationID char (10) FOREIGN KEY ( RegistrationID ) REFERENCES
PROJECT_ASSIGNED ( RegistrationID ) ) ;
Create table PROJECT_ASSIGNED ( RegistrationID char(10) Primary key , ProjectID char(10) ,
AssignDate date FOREIGN KEY ( ProjectID ) REFERENCES Project ( ProjectID ) ) ;
Create table Project ( ProjectID char(10 ) Primary key , ProjectName char(50) , SubmissionDate
date , TeamSize int , GuideTeacher char(50) );
(a) Insert the data of your choice.
(c) Select Name from STUDENT where stream = “Science” .
(d) RollNo in student table.
RegistrationID in PROJECT_ASSIGNED table .
ProjectID in project table.
(e) RegistrationID in student table.
ProjectID in PROJECT_ASSIGNED table.
(f) Select name from student , PROJECT_ASSIGNED , project group by GuideTeacher
having [Link] = PROJECT_ASSIGNED.RegistrationID and
PROJECT_ASSIGNED. ProjectID = [Link] ;
9. An organization ABC maintains a database EMP-DEPENDENT to record the following
details about its employees and their dependents.
EMPLOYEE(AadhaarNo, Name, Address, Department, EmpID)
DEPENDENT(EmpID, DependentName, Relationship)
Use the EMP-DEPENDENT database to answer the following SQL queries:
a) Find the names of employees with their dependent names.
Answer.
Select Name , DependentName from EMPLOYEE , DEPENDENT where EMPLOYEE.
EmpID = DEPENDENT. EmpID ;
b) Find employee details working in a department, say, ‘PRODUCTION’.
Answer.
Select * from EMPLOYEE , DEPENDENT where EMPLOYEE. EmpID = DEPENDENT.
EmpID and DependentName = “Production” ;
c) Find employee names having no dependent
Answer.
Select * from EMPLOYEE , DEPENDENT where EMPLOYEE. EmpID = DEPENDENT.
EmpID and DependentName is null;
d) Find names of employees working in a department, say, ‘SALES’ and having exactly two
dependents.
Answer.
Select * from EMPLOYEE , DEPENDENT where EMPLOYEE. EmpID = DEPENDENT.
EmpID and DependentName = “Sales” and Relationship = 2 ;
10. A shop called Wonderful Garments that sells school uniforms maintain a database
SCHOOL_UNIFORM as shown below. It consisted of two relations — UNIFORM and PRICE.
They made UniformCode as the primary key for UNIFORM relation. Further, they used
UniformCode and Size as composite keys for PRICE relation. By analysing the database
schema and database state, specify SQL queries to rectify the following anomalies.
Table: UNIFORM
UCode UName UColor
1 Shirt White
2 Pant Grey
3 Skirt Grey
4 Tie Blue
5 Socks Blue
6 Belt Blue
Table: PRICE
UCode Size Price
1 M 500
1 L 580
1 XL 620
2 M 810
2 L 890
2 XL 940
3 M 770
3 L 830
3 XL 910
4 S 150
4 L 170
5 S 180
5 L 210
6 M 110
6 L 140
6 XL 160
a) The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL
query to change the name of the relation PRICE to COST.
Answer.
ALTER TABLE PRICE RENAME TO COST ;
b) M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of `100
each. Insert this record in COST table.
Answer.
Insert into COST values ( 7 , “M” , 100 ) ;
c) When you used the above query to insert data, you were able to enter the values for
handkerchief without entering its details in the UNIFORM relation. Make a provision so that
the data can be entered in COST table only if it is already there in UNIFROM table.
Answer.
We have to make foreign key UCode in COST table . So, we have to write this query :-
ALTER TABLE COST ADD FOREIGN KEY ( UCode )
REFERENCES UNIFORM ( UCode );
d) Further, you should be able to assign a new UCode to an item only if it has a valid UName.
Write a query to add appropriate constraint to the SCHOOL_UNIFORM database.
Answer.
Alter table UNIFORM modify column UName char(50) Not Null ;
e) ALTER table to add the constraint that price of an item is always greater than zero.
Answer.
Alter table COST modify PRICE int check ( PRICE > 0 ) ;
;