Example HTML page

MySQL Assignment 9


Please do the following assignment. The questions are based on “date” and “time”. 

1. Create a Table with name “assessment”. The attributes of the table include “test_id”, “test_name”, “topic_name”, “date_of_the_test”, “start_at”, “end_at”.

The datatypes of the attributes are :

test_id is of int type of width “5”, with ZERO FILL and auto_increment primary key.
test_name is variable length character of size “20” NOT NULL.
topic_name is variable length character of size “20” NOT NULL.
date_of_the_test is of date type
start_at is of time data type
end_at is of time data type.

2. Insert the following records in to the table.

00001,”Assessment 1″,”C”,”2018-12-03″,08:00:00,10:00:00
00020,”Assessment 2″,”C++”,”2017-11-04″,10:30:00,12:00:00
00015,”Practice Problems 1″,”Java”,”2018-12-01″,15:30:00,17:30:00
00007,”Bonus Test”,”Python”,”2016-10-01″,14:30:00,16:00:00
00008,”Assessment 3″,”C”,”2018-12-04″,09:30:00,10:00:00

3. Insert a new record with 00010,”Practice Problems 2″,”MySQL” with Current_date and Current time as start_at time and 1 hour duration of the test as end_at.

4.  Print all the columns present in the “assessment” table.

5. Print the test_id and test_name of the topic_name like “C”.

6. Print the tests in between 2016 and 2017.

7. If we have given one week extension for the test_id 00008, print the extended date using select statement.

8.How many days are in between the “Assessment 1” and “Assessment 2”.

9.Write a query to display the duration of the test “C++”. The new column name should be “Duration” only in the result set of SQL query.

10.The test of Java is announced before ’20’ days of the test conducted. Print the resultant date i.e. on which date the test is announced.

11. The students can take the “Practice Problems 1” test with in 1 month of the test_date given. What is the end_date of this test. The column_name of the result set is “end_date“.

12. Print the date of all the tests given in the table.

13. Print the date of all the test given in the table in “YYYY/MM/DD” form.

14. Print the start_at, end_at time of the test ‘00015’.

15. Print the start_at, end_at time of the test ‘00008’ in AM/PM format.

16. Print the Year of the test ‘00001’.
Note: the output is only ‘2018’, but not ‘2018-12-03’.

17. Print all the tests in the order of “date”.

18. Print the “test_id” and “duration” of each test. Here duration is obtained by subtracting the ‘start_at‘ from ‘end_at‘.

19. Print “two” recent tests in the table. Here two recent tests are obtained by date.
Hint: you can sort with date in descending order and use “limit” keyword.

20. Copy the table in to “New_Tests“, and truncate the “assessment” table.

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest