Introduction to Structured Query Language (SQL) NCERT Solution || Introduction to Structured Query Language (SQL) Class 11 Solution || Introduction to Structured Query Language (SQL) Python Class 11 IP Solution || Introduction to Structured Query Language (SQL) Information Practices Solution || NCERT Introduction to Structured Query Language (SQL) Solution || Introduction to Structured Query Language (SQL) in Python Class 11 IP || Introduction to Structured Query Language (SQL) in Python Solution
Note :- Please Click on question to get answer of that Question !!
Q1. Match the following clauses with their respective functions.
ALTER:
Insert the values in a table
UPDATE: Restrictions on columns
DELETE:
Table definition
INSERT INTO: Change the name of a column
CONSTRAINTS:
Update existing information in a table
DESC: Delete an existing row
from a table
CREATE: Create a database
Q2. 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
b) What
does ‘name’ represent in the above code snippet?
i) a table
ii)
a row
iii) a column
iv) a database
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’
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
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
Q3. 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
b)The symbol Asterisk (*) in a
select query retrieves ____________.
i) All data from
the table
ii) Data of primary key only
iii) NULL data
iv)
None of the mentioned
Q4. Consider the following MOVIE database and answer the SQL queries
based on it.
a) Retrieve movies information without
mentioning their column names.
b) List business done by the movies
showing only MovieID, MovieName and BusinessCost.
c) List the
different categories of movies.
d) Find the net profit of each
movie showing its ID, Name and Net Profit.
(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.
f) List all movies
which fall in the category of Comedy or Action.
g) List the movies
which have not been released yet.
Q5. 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”.
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.
c) Using table level constraint,
make TeamID as primary key.
d) Show the structure of the table TEAM
using SQL command.
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)
f) Show
the contents of the table TEAM.
g) Now create another table below.
MATCH_DETAILS and insert data as shown in table. Choose appropriate
domains and constraints for each attribute.
Table:
MATCH_DETAILS
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.
Q6. 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.
b) Retrieve the MatchID of
all those matches where FirstTeam
has scored < 70 but SecondTeam has
scored > 70.
c) Find out the MatchID and date of matches played
by Team 1 and won by it.
d) Find out the MatchID of matches played
by Team 2 and not won by it.
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.
Q7. Differentiate between the following commands:
a) ALTER and
UPDATE
b) DELETE and DROP
Q8. Create a database called STUDENT_PROJECT having the following
tables. Choose appropriate data type and apply necessary
constraints.
Table: STUDENT
* 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
Table: PROJECT
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.
Q9. 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.
b) Find employee details working in a
department, say, ‘PRODUCTION’.
c) Find employee names having no
dependent
d) Find names of employees working in a department, say,
‘SALES’ and having exactly two dependents.
Q10. 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
Table: PRICE
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.
b) M/S Wonderful Garments also keeps handkerchiefs of red
color, medium size of `100 each. Insert this record in COST table.
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.
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.
e) ALTER table to add the constraint that price of an
item is always greater than zero.
Can you do assignment questions also it will be great reference!!!!
ReplyDeleteTell me Question.
DeletePost a Comment
You can help us by Clicking on ads. ^_^
Please do not send spam comment : )