XII:: Informatics Practices :: Final - Worksheet 1 – MySQL (Revision Tour)

Topic 1 :: Database Concepts

1. Define the following:

(a) Database (b) DBMS & Examples (c) Data Redundancy (d) Data Inconsistency

(e) Data Isolation (f) Data dependence (g) Database Schema (h) Database Instance

(i) Data Dictionary (j) Metadata (k) Database Constraint (l) Query

(m) Database Engine (n) Relation (o) Tuple (p) Attribute (q) Domain (r) Cardinality (s) Degree

2. Write the limitation of file based approach.

3. What are the uses of DBMS?

4. (Imp) What is the cardinality and degree of the following table student:

5. (Imp) Differentiate between DDL and DML commands.

6. (Imp) Differentiate between char and varchar commands.

7. (Imp) Define the following

(a) Primary Key (b) Candidate Key (c) Alternate Key (d) Foreign Key

Give an example table that includes all four types of keys.

8. What is a constraint? Explain the 5 types of constraint.

Database Commands

​

1. Write an SQL command to view the list of databases.

2. Write an SQL command to create a new database ‘Exams’.

3. Write an SQL command to enter into database ‘Exams’.

4. Write an SQL command to delete database ‘Exams’.

VEG Table

Write SQL statements

​

​

1) To create the above table

2) In Insert the above values

3) To see the structure of the table

4) To see all the details of the table

5) To display vegetable names and their

categories.

6) To display vegetable names and their double

price.

7) To display details of Leafy vegetables only.

8) To display vegetable number and vegetable

name whose price is more than 30;

9) To display vegetable names whose price is from

20 to 50. (Without using Between)

​

10) To display vegetable names and their prices whose

price is from 30 to 60. (Use Between Keyword)

11) To display vegetable number and vegetable name which

are purchased after 20^th January 2025.

12) To display vegetable number and vegetable name,

whose vegetable numbers are V1, V2 and V3. (use IN)

13) To display vegetable names and their prices, whose

vegetable name starts with c.

14) To display vegetable names, whose second character is

“O”.

15) To display different categories list

16) To display all categories list

17) To display vegetable names and prices in the ascending

order of price.

18) To display vegetable names and prices in the

descending order of vegetable names.

ALTER TABLE

19. To add a column “Colour” with datatype “varchar” to store 10 characters.

20. To remove primary key of the table.

21. To add primary key (vnumber)

22. To remove the column “Colour”

23. To change the datatype of price to float.

24. To change the attribute name vname to “vegetablename” and its datatype is varchar(30).

UPDATE, DELETE & DROP

25. To change the price of Spinach to 15.

26. To delete V1 and V3 vegetables details.

27. To delete all vegetable details.

28. To delete entire table (including the structure)

​

​

​

GROUP BY

29. To display the count of each category.

30. To display the count of each category, whose count is more than 2.

31. To display count of each category, whose price is more than 15.

32. To display count of each category, whose price is more than 15, whose count is exactly one.

33. To display vegetable names, Date of Purchase, Count based on the DOP column.

Queries & Outputs

1) To get the squares of Prices.

2) To get the value 7³.

3) To get the Reminder of the expression 15 divided by 7.

​

(4) Output

SELECT ROUND(74628.68523,1)

SELECT ROUND(74628.68523,2)

SELECT ROUND(74628.68523,3);

SELECT ROUND(74628.68523,4);

SELECT ROUND(74628.68523,5);

SELECT ROUND(74628.68523,6);

SELECT ROUND(74628.68523,0);

SELECT ROUND(74628.68523);

SELECT ROUND(74628.68523,-1);

SELECT ROUND(74628.68523,-2);

SELECT ROUND(74628.68523,-3);

SELECT ROUND(74628.68523,-4);

SELECT ROUND(74628.68523,-5);

SELECT ROUND(74628.68523,-6);

5) To display Vegetable Names in capital letters

6) To display the string “Welcome to AP” in capital letters

7) To display Category in lower case letters.

8) To display the string “Welcome to AP” in lower case letters.

9) To display 2^nd character onwards totally 3 characters in vegetable names (Vegetable names also should display)(Use substring, substr, mid)

10) To display length of vname, price and DOP

11) To display length of “India is best country”.

12) To display first 3 characters in vegetable names.

13) To display first 10 characters from “INDIA is best country”.

1. Get the current date and time using NOW()

2. Get only the current date using DATE()

3. Extract the month from the DOP column using MONTH()

4. Get the month name from DOP using MONTHNAME()

5. Extract the year from DOP using YEAR()

6. Extract the day of the month from DOP using DAY()

7. Get the weekday name from DOP using DAYNAME()

8. Get all vegetables that were purchased in January

9. Get vegetables purchased on a Saturday

10. Get the vegetables that were purchased in the current year (assuming current year is 2025)

Date & Time Functions

1) SELECT VNAME, RIGHT(SUBSTR(VNAME,2,4),3) FROM VEG;

2) SELECT POW(RIGHT(VNUMBER,1),2) FROM VEG;

3) SELECT VNAME, UCASE(LEFT(MID(VNAME,2),4)) AS RESULT FROM

VEG WHERE PRICE>45;

4) SELECT VNAME, MOD(LENGTH(UCASE(MID(VNAME,2))),2) AS RESULT FROM VEG;

Combination of these functions

Vnumber Vitamin

V1 C

V2 B6

V3 A

V4 C

V5 K

V6 A

VegVit

Veg

Equi Join

1. Retrieve all vegetable details along with their vitamins.

2. Find the price of vegetables that contain Vitamin C.

3. List all leafy vegetables along with their vitamins.

4. Retrieve vegetables that contain Vitamin A or Vitamin K.

5. Find the total number of non-leafy vegetables that contain Vitamin C.

6. Display vegetables purchased in 2025 along with their vitamin type.

7. Retrieve vegetables that contain the same vitamin as "Onion".

​