KEMBAR78
MySQL Practical 6 | PDF | Databases | Data Management
Scribd
Upload
112 views3 pages

MySQL Practical 6

This document provides instructions on sorting tables and views in MySQL. It discusses sorting syntax for ascending and descending order. Examples are given to sort the Supplier and Offers tables. The document also covers creating views from tables and examples are provided to create views from the Offers and Customer tables. Finally, exercises are provided to perform queries on sample Lecturer, Course, and Class tables, including sorting, inserting, updating, deleting, listing, and creating a view.

Uploaded by

crybert zinyama
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
112 views3 pages

MySQL Practical 6

This document provides instructions on sorting tables and views in MySQL. It discusses sorting syntax for ascending and descending order. Examples are given to sort the Supplier and Offers tables. The document also covers creating views from tables and examples are provided to create views from the Offers and Customer tables. Finally, exercises are provided to perform queries on sample Lecturer, Course, and Class tables, including sorting, inserting, updating, deleting, listing, and creating a view.

Uploaded by

crybert zinyama
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

M.

Chinyuku Database Systems 2023

MySQL Practical 6- Sorting Tables and


Views
Exercise
Use the “exercise 4” database you created in Practical 4

To sort in ascending order


Syntax

SELECT field1, field2, …, fieldn


FROM table_name
ORDER BY fieldk;

1. Sort Supplier table by supplier name

SELECT SupplierNumber, SupplierName, SupplierAddress, City


FROM Supplier
ORDER BY SupplierName;

To sort in descending order


Syntax

SELECT field1, field2, …, fieldn


FROM table_name
ORDER BY fieldk DESC;

2. Sort Offers table order by price in descending order

SELECT OfferID, ProductID, SupplierNumber, Price


FROM Offers
ORDER BY Price DESC;

Page 1|3
M. Chinyuku Database Systems 2023

Views
• Views may be queried, combined in queries with base relations and used to define other views.

• Database views can be created using the CREATE VIEW statement.

• Views can be created from a single table, multiple tables, or another view.

• To create a view, a user must have the appropriate system privilege according to the specific
implementation.

VIEW SYNTAX
CREATE VIEW view_name AS
SELECT column1, column2......
FROM table_name
WHERE [condition];

1. Create a view from Offers table to view only ProductID and Price

CREATE VIEW View1 AS


SELECT ProductID, Price
FROM offers;

2. Create a view that lists all customer names from Chinhoyi only

CREATE VIEW View2 AS


SELECT FirstName, LastName
FROM customer
WHERE City = ‘Chinhoyi’;

Exercise
Retrieve the database you created in Practical 3 with the following tables:

LECTURER Table
LecturerID LecturerNam Office
e
E1001 Chinyuku E7
E1002 Kavu E8
E1003 Rugube E10
E1004 Phiri E8
E1005 Moyo E26

Page 2|3
M. Chinyuku Database Systems 2023

COURSE Table
CourseID CourseName Department
CUIT201 Database ICT
CUMT105 OR Maths
CUIT206 OOP ICT
CUIT208 Software Eng ICT
CUPE105 Logic Design Engineering
CUIT213 Data Comm Engineering
CUEB401 Database Business

CLASS Table
ClassI LecturerI CourseID Day Time
Venu
D D e
C1001 E1002 CUIT206 Monday 0800 Tlab
C1002 E1001 CUIT201 Monday 1000 Tlab
C1003 E1005 CUPE105 Wednesday 1400 NEC2
C1005 E1004 CUMT105 Friday 1200 NEC3

Use the database to perform the following queries:

a) Insert a new lecturer with the following details


LecturerID= E1010
LecturerName = Moyo
Office = E26
b) The Database course in the Business department is renamed to Introduction to Computers
c) All lectures on Friday have been suspended, therefore, you are required to remove all affected
classes from the databases.
d) List courses from both the ICT and Maths department
e) List all lecturer names
f) Who is in office E8
g) Sort the Class table in Descending order by ClassID
h) List all course names that start with a letter D
i) Create a view for CourseID and Day from the Class table

Page 3|3

You might also like