CSC1213 Practical Assignment 13 2022
Database Management Systems (DBMS) - SQL using MySQL Server
Log on to the MySQL server using given user name and password.
1. Create a blank database as “Assignment13”.
2. Create following three tables.
company vehicle repair
c_code c_name v_code v_brand v_type r_c_code r_type
c01 Ruwan Enterprise c02 Toyota Car c06 Car
c02 Indra Traders c01 Toyota Car c01 Van
c03 Weels Lanka c01 Mazda Car c04 Bus
c04 Toyota Lanka c02 Toyota Van
c05 Devid Peris c02 Toyota Double Cab
c06 Auto Service c03 Mazda Van
c04 Toyota Bus
c03 Toyota Car
c04 Micro Car
c05 Chery QQ Car
3. Using the union operator, get the list of company codes who sale vans and buses.
select v_code from vehicle where v_type ='Car'
union
select v_code from vehicle where v_type ='Bus';
4. Output the result of above query using “union all” operator and identify the difference between “union”
and “union all”
select v_code from vehicle where v_type ='Car'
union all
select v_code from vehicle where v_type ='Bus';
5. Using union operator, get a list of all company codes who sale and repair vans
select v_code from vehicle where v_type='Van'
union
select r_c_code from repair where r_type='Van';
6. Develop the above query to find the names of those companies.
select c_name from vehicle, company where v_type='Van' and v_code=c_code
union
select c_name from repair, company where r_type='Van' and r_c_code=c_code;
© DCS@RUH 1
Prepared by – Anil Luvishewa
7. Join the company and vehicle table by using “JOIN” keyword.
SELECT v_code, c_name FROM company
INNER JOIN vehicle
ON c_code=v_code
8. Do the above query using “INNER JOIN” operation and investigate the result against previous result.
9. Modify the above query to display the company codes without repeats in ascending order.
SELECT distinct v_code, c_name FROM company
JOIN vehicle
ON c_code=v_code
ORDER BY c_name asc;
10. Display fields of v_code,v_brand,v_type, r_c_code and r_type by joining vehicle and repair tables using the
LEFT JOIN.
11. Do the above query using the RIGHT JOIN and check the differences of both results.
12. Create an index called comp_index on the company code of company table
create index comp_index on company(c_code);
13. Using the above index, select details of “c04” company. (check the time taken to process your query)
SELECT * FROM company USE INDEX (comp_index) where c_code='c04';
14. Display the indexes available on company table.
SHOW INDEXES FROM company;
15. Drop the comp_index from your database.
DROP INDEX comp_index ON company;
DROP INDEX `PRIMARY` ON company;
16. Display again indexes available on company table.
17. Create a backup table called vehicle_backup on vehicle table.
18. Delete data available on company table.
© DCS@RUH 2
Prepared by – Anil Luvishewa