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)