Example HTML page

MySQL Assignment 10

 

 

 

Hi,

The following questions are based on “enum” data type, group by and order by.

1. Create a table with name “shirts”.  The attributes of the table include ‘name’ and ‘size’.

‘name’ is of variable length character of size 30.

‘size’ is a enumerated data type; accepted values are : ‘x-small’, ‘small’, ‘medium’, ‘large’, ‘x-large’.

2. Insert the following records in to the table.

(‘dress shirt’,‘large’)

(‘t-shirt’,‘medium’)

(‘polo shirt’,‘small’)

(‘t-shirt’,‘x-large’)

3. Insert the following records in to the table.

(‘dress shirt’,1)

(‘t-shirt’,3)

(‘polo shirt’,4)

(‘dress shirt’,2)

(‘t-shirt’,5)

(‘polo shirt’,1)

4. Insert the following records in to the table. note the error, if you find any trouble while inserting the following records.

(‘dress shirt’,9)

(‘t-shirt’,8)

(‘t-shirt’,null)

(‘t-shirt’,‘average’)

(‘polo shirt’,‘very small’)

5. Display the ‘medium’ size shirts.

6. Display the shirts whose index is ‘2’.

Hint: constraint in the where clause is : where size=2.

7. Update the shirts with size ‘small’ to ‘large’.

8. What is the output of the following query. Any reason?

select * from shirts where size=0;

9. What is the difference between the following two queries.

Query 1: select size from shirts;

Query 2: select size+0 from shirts;

10. Run the following queries and write the difference between Query 1 and Query 2, Query 3 and Query 4.

Query 1: show columns from shirts;

Query 2: desc shirts;

Query 3: show columns from shirts like ‘size’;

Query 4: show columns from shirts like ‘s%’

11. Count the shirts of each category.

12. Count the shirts of each category in ascending alphabetical order.

13. Count the shirts of each category in descending alphabetical order.

14. Count the shirts of each category in the order of ‘small’, ‘x-small’, ‘medium’, ‘x-large’, ‘large’.

15. The following question is based on the limitation of ‘enum’

Run the following queries and figure out one sentence from it.

Query 1:

CREATE TABLE sizes ( size ENUM(‘small’, CONCAT(‘med’,‘ium’), ‘large’) );

Set statement:

SET @mysize = ‘medium’;

Query 2:

CREATE TABLE sizes ( size ENUM(‘small’, @mysize, ‘large’) );

Example HTML page

Leave a Reply

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

Pin It on Pinterest