Sunday 22 October 2017
Monday 16 October 2017
SQL WORKSHEET 2017-18
SQL
WORKSHEET 2017-1888
computer
Science
1)
|
Observe the following STUDENTS and EVENTS
tables carefully and write the
name of the RDBMS operation which will be used
to produce the output as shown in LIST. Also, find the Degree and Cardinality
of the LIST.
  |
||||||||||||||||||
2)
|
Answer the following
questions:
|
||||||||||||||||||
a)
|
Expand the following:
(i) SQL (ii) DBMS
|
||||||||||||||||||
b)
|
What is relational database model?
|
||||||||||||||||||
c)
|
What are all the numeric data types
possible in sql?
|
||||||||||||||||||
d)
|
Differentiate between char and varchar.
|
||||||||||||||||||
3)
|
Define the following keys:
a)Primary key b) Candidate key c)Alternate key
|
||||||||||||||||||
4)
|
Write
any two advantages of SQL.
|
||||||||||||||||||
5)
|
Differentiate between
a)Union
and Cartesian product
b)DDL
and DML commands
|
||||||||||||||||||
6)
|
Answer the following questions:
|
||||||||||||||||||
a)
|
Ms. Ravina is using a table
'customer' with custno, name, address and phonenumber. She needs to display
name of the customers, whose name start with letter 'S'. She wrote the
following command, which did not give the result.
Select * from customer where
name="S%";
Help Ms. Ravina to run the query by removing the errors and
write the correct query.
|
||||||||||||||||||
b)
|
Write
SQL query to add a column totalprice with data type numeric and size 10, 2 in
a table product.
|
||||||||||||||||||
c)
|
The
name column of a table 'student' is given below.
Based on the information, find the output
of the following queries:
Select name from student where name like
"%a";
|
||||||||||||||||||
d)
|
Sonal needs to display name of teachers,
who have "o" as the third character in their name. She wrote the
following query.
Select name from teacher Where name
="$$o?";
But the query is not producing the result.
Identify the problems and correct it.
|
||||||||||||||||||
e)
|
Deepak wants to remove all rows from the
table Bank. But he needs to
maintain the structure of the table. Which command is used to implement the
same?
|
||||||||||||||||||
7)
|
In a database there are
two tables - 'Brand' and 'Item' as shown below:
  |
||||||||||||||||||
Write
MYSQL queries for the following:
a) To display Iname, price and
corresponding Brand name (Bname) of those items, whose price is between 25000
and 30000 both values inclusive).
b)
To display ICode, Price and BName of the item, which has IName as
"Television".
c) To increase the Prices of all those
items by Rs 10% which have a brand name.
d) To add a column qty of numeric type to
ITEM table.
e)To remove those records from ITEM table
whose INAME start with the letter ‘C’.
|
|||||||||||||||||||
8)
|
|
||||||||||||||||||
9)
|
Write SQL queries for (i) to
(iv) and find outputs for SQL queries (v)to (viii), which are based on the
tables.
|
||||||||||||||||||
 |
|||||||||||||||||||
(i)
To display all details from the table MEMBER in descending order of
ISSUEDATE.
(ii)
To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
(iii)
To display the DTYPE and number of DVDs in each DTYPE from the table DVD.
(iv)
To display all NAME and ISSUEDATE of those members from the table MEMBER who
have DVDs issued (i.e., ISSUEDATE) in the year 2017.
(v)
SELECT
MIN(ISSUEDATE) FROM MEMBER;
(vi)
SELECT
DISTINCT DTYPE FROM DVD;
(vii)
SELECT
D.DCODE, NAME, DTITLE FROM DVD D, MEMBER M WHERE D.DCODE=M.DCODE;
(viii)
SELECT
DTITLE FROM DVD WHERE DTYPE NOT IN("Folk", "Classical");
|
|||||||||||||||||||
10)
|
Create a table STUDENT
with the following fields.
AdmNo integer , Name 20 characters , Class
integer, AvgMks numeric,
Grade character, Dateofadm Date
Ensure that the
following constraints are met
Default value of Grade is NULL
|
||||||||||||||||||
11)
|
Insert AdmNo, name, class, AvgMks
and Dateofadm of the students in the table as given below
|
||||||||||||||||||
|
|||||||||||||||||||
The End
|
Subscribe to:
Posts (Atom)