DATABASE DESIGN - SQL TABLES

Assignment 5

Review the different types of SQL Queries that we have performed in class and other coursework.

1. Using the tables that were created in Assignment 2-4, run a series of queries against the table as shown below:

a. Run a SELECT query to return all records in the table.

Example: SELECT * FROM tbl_students;

b. Run a SELECT query that returns a single attribute from all records:

Example: SELECT student_ID FROM tbl_students;

2. Using the same tables perform queries to extract information about a single records:

a. Run a SELECT query that returns ONE complete record in the table:

Example: SELECT * FROM tbl_students WHERE student_ID=’wuv01’;

b. Run a SELECT query that returns two or more attributes of a single record, but not all of the attributes:

Example: SELECT Student_ID, Student_FNAME, Student_LNAME FROM tbl_students WHERE student_ID =”wuv01”;

3. Using the same tables create a derived attribute:

a. Using SELECT query create a derived attribute showing the total records in table:

Example: SELECT count(*) as Total_Records FROM tbl_students;

b. Use a select query to return the total records where a condition is true:

Example: Select count(*) as Total_Records FROM tbl_Students WHERE student_zip=22031;

Lab 5:

1. Using the tables created in Assignment 2-4, modify one of the records in the table using an UPDATE query.

Syntax for UPDATE: Update <table> SET <attribute> = value WHERE <condition>;

2. Using one of the tables created in Assignment2-4, remove one of the records using a DELETE query.

Syntax for DELETE: DELETE FROM <table> WHERE <condition>;

3. Use a SELECT query to create a derived attribute from one attribute in the table

For example, to derive AGE you might perform the following query:

SELECT student_ID,

student_FNAME,

student_LNAME,

(year(Curdate()) - year(student_DOB)) AS AGE

FROM tbl_students;

4. Use a SELECT query to show two attributes from two tables (the SQL output should show a total of 4 attributes). This is an example of a JOIN:

Example:

SELECT tbl_students.student_FNAME, tbl_students.student_LNAME, tbl_courses.course_id, tbl_courses.course_desc FROM tbl_students, tbl_courses where tbl_students.student_ID = tbl_courses.student_ID;

+---------------+---------------+-----------+--------------------+

| student_FNAME | student_LNAME | course_id | course_desc |

+---------------+---------------+-----------+--------------------+

| David | Jones | CS531 | Databse Design |

| David | smith | CS531 | Databse Design |

| David | smith | CS500 | Into to Networking |

+---------------+---------------+-----------+--------------------+

3 rows in set (0.0015 sec)

Bonus: Many times, data will be entered in a table in non-alphabetic order. Display the tbl_students table created in Assignment 2-4, that is NOT sorted. That is displayed as entered.

Next, Sort the table by Lastname.

HINT: Use the ORDER BY directive in the SQL statement.