CS222 – Database Management Systems paper solution step by step.
Question 4: Write queries for the following:
a) Create a table for an entity type you mentioned in Question – 3 above. There must be at least
four attributes in the table. The first Attribute should be PK. The second attribute should be NN.
The third attribute must have a Default Value.
Answer: create table owner(CNIC varchar(255) primary key, name varchar(255) not null, Domicile varchar(255) default “Kohat”, Address varchar(255));
b) Insert TWO rows in the table you created. Use a single query and insert values into the first two
columns only.
Answer: insert into owner() values (“17980-3933933-9″,”Mr John”,”,”),(“180342-3937384-7″,”Mr Keter”,”,”);
c) Create another table from this table. This table should have only the first two columns. Also,
use a WHERE clause to filter some records.
Answer: create table kust AS (select * from owner where name = “Mr John”)
d) Create a user of your own name. Give SELECT privilege to the created user on the first column
of the table that you created in Query no. (a).
Creating User in Sql
create user username
Assigning Select priviliges to all the table
GRANT SELECT ON databaseName.tableName TO username;
e) Add a new column in the created table. Name this column as [YOUR-FATHER-NAME] with
a datatype of your own choice.
alter table owner add Your_Father_Name varchar(255);
f) Create a VIEW and then Rename it.
create view View Name as select * from table Name;
g) Suppose we have a table having product names. Display the list of products having names
starting with ‘S’ and ending with ‘g’.
select * from owner where name like ‘s%g’;
