IP HOMEWORK JANUARY 2017

HOMEWORK 1 :
SUBMISSION DATE 10/01/2015
1A)Differentiate between DDL and DML queries in SQL.
1 b) A relation in MySQL has 5 attributes and 8 tuples. What will be the cardinality and degree of the relation?
1 c) What is the difference between Candidate key and Alternate Key in a Relation?

HOMEWORK 2
SUBMISSION DATE 15/01/2015
 2 a) Give one word answer to the following questions:
 i. An attribute that is a Primary key of one table and used as non‐key attribute in another table.
ii. A SQL command used to display the structure of a table in MySQL
iii. A SQL command used to remove duplicate rows from a SELECT query.
 iv. An SQL query that uses SET command to make modification on table data.

2 b) What is a Column alias in SQL? Give an example of using column alias in a query

HOMEWORK 3
SUBMISSION DATE 25/01/2017

Q.3 a) What will be the output of the following SQL queries:
 i. SELECT MID('AVATAR',3,2)
 ii. SELECT CHAR(66,65,68)
 iii. SELECT DAYOFYEAR('2011‐12‐31')
 iv. SELECT ROUND(15.15, ‐1)

HOMEWORK 4 
 4 ) Study the following table’s  structure and answer the given questions.

Column Name	Data type	Size	Constraints	Description
V_id	BIGINT	8	Primary key	Voter identification
Vname	VARCH AR	25	Not null	Name of the voter
Age	INT	3	Check>17	Age should not less than equal to 17
Address	VARCH AR2	30		Address of voter
Phone	VARCH AR	10		Phone number of the voter

write SQL statements to create the above table.

(i)             Write the command to delete all the rows of particular voter from the table voter where voter ID between 10 and 20.

(ii) Display the voter details in descending order of voter names

(iii)Display the names of all voters between 17 and 25 years.

(iv) Display all voter names ending to a.

(v)Delete the voter with V_id=123456

5)Study the following table’s structure and answer the given questions.
Table: STUDENT
Column name        Data type         Limit                  Constraint
ROLLNO                Integer             4 digits                Not Null
SNAME                 Varchar              25 characters       Not Null
GENDER               Char                    1                      Not Null
DOB                     Date                                            Not Null
HOUSEID             Integer                  2                      Not Null
FEES                   Integer                4 digits                Not Null
HOBBY                Varchar               15 characters         Null
Primary Key is ROLLNO
i. Write SQL query to create the STUDENT table. (2)
ii. Write SQL query to increase the size of the SNAME to hold 30
characters (2)

iii. Write SQL query to insert a row in the student table. (2)