MySQL Assignment on Create Table


MySQL Assignment on Create Table Command. These three commands are part of SQL DDL commands. This assignment is based on these three basic DDL commands but I am sure after going through this MySQL assignment on Create Table command, you will receive something special that is not given in the books.

Attempt this assignment on any version of mysql.

Q1. Show all the available database on your MySQL server.

Q2. Create a database ‘school’

Q3. Change the current database to school

Q4. Show all the available tables in your current database.

Q5. Write SQL command to display your current database

Q6. Create a table student with the following specifications
S.no FieldName Data Type Size Constraints
1 Admno Char 10
2 Name VARCHAR 30 Not Null
3 Stream Varchar 20 NOT null
4 Gender Char 1
5 Marks Float 6,2

Q7. Add a new column email and phone number in the above table studentalte with the following specifications

s.no Field name Data type Size Constraint
1 Email Char 80
2 Phone Integer 10

Alter Table Command

Alter Table command used to make changes in the current Table structure.

Q8. Change the data type of phone no from integer 10 to char 15.

Q9. Add primary key( admno ) in the above table using alter table command

Q10. Add a new column rollNo integer(4) in the above table using alter table command

Q11. Drop the primary key from the above table using alter table command

Q12. Add a primary key( admno + roll no) in the above table student

Q13. Create a table employee with the following specifications

s.no Field name Data Type Size Constraints
1 Empcode Number 10 Primary key
2 Department Char 30 Primary key
3 Name Char 30 Not null
4 Salary Number 8,2 Not null
5 Designation Char 30 Not null
6 Doj Date

Q14. Show the structures of above-created tables ‘student’ and ‘employee’

Q15. Create a new table ‘duplicate’ with the same data structure as ‘student’

Q16. Rename the column ‘doj’ as ‘date_of_joining’ in the employee table.

Q17. Delete the structure as well as the table duplicate from the database school.

Q18. Is there any differentiation between DESCRIBE and DESC command that displays the structure of a table?

Q19. Employee Table has a composite primary key emplcode+department. How we can change this to ‘empcode’ only. ( ie drop department from primary key)

All the above questions were framed after a long discussion with the students, You can refer to MySQL important commands as well as MySQL Create, Alter table command tutorial for more information.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.