DDL command worksheet no 3

MY SQL WORK SHEETS
Informatics Practices
My SQL Worksheet-3
(DDL – Alter Table commands)
1. Sohan created a table in Mysql. Later on he found that there should have been another column in
the table. Which command should he use to add another column to the table?
2. While creating a table 'Customer' Simrita forgot to set the primary key for the table. Give the
statement which she should write now to set the column 'CustiD' as the primary key of the table?
3. Kuhu has already created a table ‘Hospital’ as shown below:
Now she wants to add a new column ‘Address’ to the above given table. Suggest suitable MySQL
command for the same.
4. Write SQL command to remove column named ‘Hobbies’ from a table named ‘Student’.
5. While creating the table Student last week, Ms. Sharma forgot to include the column Game Played.
Now write a command to insert the Game Played column with VARCHAR data type and 30 size into
the Student table?
6. Kunal created the following table with the name ‘Friends’ :

Table : Friends
FriendCode Name Hobbies
F101 Bijoy Swimming
F102 Abhinav Reading books
F103 Jyotsna Dancing
Now, Kunal wants to delete the ‘Hobbies’ column. Write the MySQL statement
7. Rashi wants to add another column ‘Hobbies’ with datatype and size as VARCHAR(50) in the already
existing table ‘Student’. She has written the following statement. However it has errors. Rewrite
the correct statement.
MODIFY TABLE Student Hobbies VARCHAR;
8. Ms. Shalini has just created a table named “Employee” containing columns
Ename, Department, Salary.
After creating the table, she realized that she has forgotten to add a primary key column in the
table. Help her in writing SQL command to add a primary key column empid. Also state the
importance of Primary key in a table.
Downloaded from www.successmantrablog.blogspot.com Youtube: successmantra007

MY SQL WORK SHEETS
9. While creating a table 'Customer' Simrita wrongly added a primary key constraint to the field
“CUSTNAME”. Now she wants to remove the primary key constraint from the custname field. Help
her in writing the correct command.
10. Mr. Akshat have added a not null constraint to the “name” field in “employees” table. But now he
wants to remove that not null constraint. Write the command to delete the not null constraint
from name field.
11. 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
12. b) What does ‘name’ represent in the above code snippet?
i) a table ii) a row iii) a column iv) a database
13. 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’
14. d) What will be the output of following query?
INSERT INTO student
VALUES (“Shaurya”,109,’M’),
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
15. 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)
2rows
16.
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 –

17. a) Retrieve movies information without mentioning their column names.
18. b) List business done by the movies showing only MovieID, MovieName and BusinessCost.

MY SQL WORK SHEETS
19 c) List the different categories of movies.
21 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?
22 e) List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID,
Name and ProductionCost.
23 f) List all movies which fall in the category of Comedy or Action.
24 g) List the movies which have not been released yet.
25 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”.
26 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.
27 c) Using table level constraint, make TeamID as primary key.
28 d) Show the structure of the table TEAM using SQL command.
29 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)
30 f) Show the contents of the table TEAM.
31 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.

MY SQL WORK SHEETS
32 b) Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but SecondTeam
has scored > 70.
33 c) Find out the MatchID and date of matches played by Team 1 and won by it.
34 d) Find out the MatchID of matches played by Team 2 and not won by it.
35 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.
Extra Notes

DDL command worksheet no 3

Uploaded by

Copyright:

Available Formats

DDL command worksheet no 3

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DDL command worksheet no 3

Uploaded by

Copyright:

Available Formats

MY SQL WORK SHEETS

3. Kuhu has already created a table ‘Hospital’ as shown below:

6. Kunal created the following table with the name ‘Friends’ :

Downloaded from www.successmantrablog.blogspot.com Youtube: successmantra007

13. c) What is true about the following SQL statement?

15. e) In the following query how many rows will be deleted?

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 –

Downloaded from www.successmantrablog.blogspot.com Youtube: successmantra007

23 f) List all movies which fall in the category of Comedy or Action.

24 g) List the movies which have not been released yet.

25 5. Suppose your school management has decided to conduct cricket matches

26 b) Create a table “TEAM” with following considerations:

27 c) Using table level constraint, make TeamID as primary key.

28 d) Show the structure of the table TEAM using SQL command.

30 f) Show the contents of the table TEAM.

Downloaded from www.successmantrablog.blogspot.com Youtube: successmantra007

Downloaded from www.successmantrablog.blogspot.com Youtube: successmantra007

You might also like