Introduction to MySQL

Environment
Pt.1
Prof. Olufunke Oladipupo
Mr. Chukwuebuka Ejiobih
Mr. Henry Ogbu
Lecture Outline
• DDL commands
• To create a database objects
• CREATE

• DML commands
• To manipulate data of a database objects part 1
• INSERT Statements
• SELECT Statements
• UPDATE Statements
Lecture Objectives

• At the end of this lecture; students should be able to

• To understand DDL commands and use the
CREATE, ALTER and DROP commands efficiently
• The be able to manipulate a given database
practically using the DML commands such as
SELECT etc
DDL, DML, DCL, and the database
development process
DATABASE CREATION IN MySQL
A database can be created with the SQL statement:

CREATE DATABASE databasename;

However, always check if a database already exist in the

MySQLserver with the query:

SHOW DATABASES;
Selecting a DATABASE
You must select a database to work with from the server
with the query:

USE nameofdatabase;
DATATYPES in MySQL Environment

(SQL Server
Data Types
link)

ISO SQL Data Types

SQL (Relational Model Terms)
Table as Relation

Row as Tuple

Column as Attribute
DDL
• The main SQL command for data definition

• CREATE statement
• create schemas, tables (relations), and domains
(as well as other constructs such as views, indexes,
assertions, and triggers).
Main SQL DDL
• CREATE SCHEMA DROP SCHEMA
• CREATE/ALTER DOMAIN DROP DOMAIN
• CREATE/ALTER TABLE DROP TABLE
• CREATE VIEW DROP VIEW
• Many DBMSs also provide: CREATE INDEX DROP
INDEX
DDL cont.
• Environment
• DBMS
• Catalog.
• a named collection of schemas in an SQL
environment
• Schema
• Domain
• Objects in a schema
• Tables, Views, Domains, assertions , etc
• All have same owner
CREATE SCHEMA
CREATE SCHEMA Name_of_Schema;

DROP SCHEMA Name_of_schema [RESTRICT |

CASCADE ];

Example 1
CREATE SCHEMA COMPANY;
CREATE TABLE
CREATE TABLE COMPANY.EMPLOYEE ... Explicit

rather than

CREATE TABLE EMPLOYEE ... Implicit

CREATE TABLE
• Creates a table with one or more columns of the
specified dataType.
• With NOT NULL, system rejects any attempt to insert a
null in the column.
• Can specify a DEFAULT value for the column.
• Primary keys should always be specified as NOT NULL.
• FOREIGN KEY clause specifies FK along with the
referential action
CREATE TABLE Example
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY (Ssn));
To Rename a Table

RENAME TABLE Old_Table_Name to New_Table_Name;

Example: To rename the table EMPLOYEE in the previous

slide to WORKER, use:

RENAME TABLE EMPLOYEE to WORKER;

DROPPING A TABLE
To drop a table use:

DROP TABLE table_name;

Example: DROP TABLE EMPLOYEE;

TO ALTER A TABLE ( Add a new
column)
To alter a table use:

ALTER TABLE table_name ADD name_of_newColumn

DATATYPE(size);

Task: Add a new column /attribute to the WORKER TABLE.

New attribute should be named email with datatype
VARCHAR(50)
Modifying the datatype of a column

ALTER TABLE table_name MODIFY COLUMN

column_name DATATYPE(size);

Task: Modify the datatype of the column email from

VARCHAR(50) TO VARCHAR(100)
Changing the position of a column
To move the position of a column use:

ALTER TABLE table_name MODIFY

column_name_to_be_moved DATATYPE(size) AFTER
target_column;
Example
• Change the position of column ssn in the table
employee to become the first attribute on the table.
SOLUTION
Solution: Use the query;

ALTER TABLE employee

MODIFY COLUMN ssn char(9) FIRST;
The result of the query can be seen in Table 1.2

Table 1.2: Modified Table, repositioning SSN as the first attribute

Dropping a column
• To drop a column use:

ALTER TABLE table_name

DROP column name_of_column_tobedropped;
Example
• Drop the column Dno in the employee table
Solution
Solution: Use the query;

ALTER TABLE employee

DROP COLUMN Dno;
Solution
• Result: The column Dno has been dropped from table
employee
Inserting rows into a table
• To insert rows into a table, use:

INSERT into table_name

VALUES (values separated by comma);
Updating data in a table
• To update data in a table, use:

Update table_name
SET column_to_update = newValue
WHERE identifier=value;
Unique Constraint
• The unique constraint is used to show that all values in a column
are uniquely different.

• Example:

CREATE TABLE table_name(

product_id INT, PRIMARY KEY NOT NULL,

Product_name VARCHAR(30) UNIQUE,

Amount DECIMAL (4,2)

);
Unique Constraint
• If you forgot to include the unique constraint at the
point of creating a table, you can add it by using:

ALTER TABLE table_name

ADD CONSTRAINT UNIQUE(attribute_to_be_unique);
NOT NULL Constraint
• To include the NOT NULL constraint to an attribute on a
table that has already been created, use the query:

ALTER TABLE table_name

MODIFY column_Inview datatype (size) NOT NULL
Check Constraint
• A check constraint is a rule that specifies the allowable
values in the column of every row of the base table.

• Example: create a check constraint on the EMPLOYEES

table to ensure that minimum salary of employees is
10000
Check Constraint
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2) CONSTRAINT chk_Salary CHECK(salary>=10000.00),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY (Ssn));
Check Constraint
• To add a check constraint to a table that already exist,
use the query:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK( logical
condition);
Default Constraint
• The DEFAULT CONSTRAINT is used to set a default value for a
specified column in a table.

• Example: Create a table named SKILL with attributes Skill_Id

INT, ssn char(9), Skill_Type varchar(50), Number_of_Skill INT.
Set the default Skill_Type as Coding.

CREATE TABLE SKILL( skill_Id INT PRIMARY KEY NOT NULL,

Ssn char(9),
skill_Type varchar(20) DEFAULT "Coding",
Number_of_Skill INT);
Insert Values into a Table with
DEFAULT Constraint
• The columns must be specified after the insert into
table_name clause otherwise, the default constraint
won’t take effect.

• Example:
Insert into Skill( skill_Id, ssn, Number_of_Skill)
Values( 010, 3, 9),
( 011, 9, 2),
( 012, 6, 12);
Exercise

• Insert a row into the skill table with the value of ssn as 3
• a) query the table skill to display its content
• b) Apply the unique constraint on the column ssn (state
your observation)
• c) delete the record where the skill_Id is 010 and repeat
the step in (b)
• d) Try to insert a new record with ssn value of 3 again
and interpret the outcome
Timestamp using default Date and
Time
• Timestamp datatype is an 8-byte value representing a
date and time value in the format: YYYY-MM-DD and
HH:MM:SS

• Example: Create a transaction table with attributes

Transaction_no INT primary key Not Null, Product
varchar(50), Amount decimal(9,2), DateTime Default
Now()
Solution

CREATE TABLE transaction (Transaction_no INT PRIMARY

KEY NOTNULL,
Product varchar(50),
Amount decimal(9,2),
Transaction_Date DateTime Default
Now()
);
Inserting Multi Rows to Transaction
Table
INSERT INTO transaction (Transaction_no, Product ,
Amount)
values (8, "Apple", 200.20),
(2, "sharwama", 2500),
(3, "Diaper", 5500),
(4, "Calculator", 7000),
(5, "T-Shirt", 15000);
Result of Query Using Timestamp
• Transaction Table showing the default Timestamp for each transaction
AUTO_INCREMENT CONSTRAINT
• Auto_Increment constraint is used to automatically
increment the value of the column it is applied to.
• It works only for KEY ATTRIBUTES

• Example 1: Create a customer table consisting of

attributes: customer_Id of type integer, Fname of type
varchar(30), Lname of type Varchar(30) and Address of
type varchar(200).
• i. The customer_Id should be primary key and set to
auto_Increment
SOLUTION to example 1
CREATE TABLE customers ( customer_Id INT PRIMARY
KEY AUTO_INCREMENT,
FName varchar(30),
LName varchar(30),
Address varchar(200)
);
SOLUTION to example 1
INSERT INTO customers(FName, LName, Address)
VALUES ("Henry", "Ogbu", " Flat I1, New Estate"),
(“Sinmisola", “Abiona", " Flat Z4, Esther Hall"),
(“Barnabas", “Fortunatus", " Flat A1, Daniel Hall"),
(“Joseph", “Alamu", " Room D101, Joseph Hall"),
(“David", “Ukachi", " KM 5, Lekki Phase 2"),
(“Nwafor", “Caleb", " No.10, Ring Rd, Benin-City"),
(“Okon", “Favour", " Snake Island, Apapa, Lagos");
Result of Example 1
• Result of query
FOREIGN KEY CONSTRAINT
• MySQL supports foreign key constraint, which permits
cross-referencing related data across tables and foreign
key constraint which help keep the related data
• Foreign key relationship involves a base table that holds
the initial column values and the child table with column
values that references the base column values
Example 2
Create a transaction table with attributes: Transaction_no
INT, Product varchar(50), Amount decimal(9,2),
Transaction_Date DateTime Default Now(), customer_ID
INT.
I. The primary key should be Transaction_no and it
should accept no null value
II. Specify the customer_ID attribute as the foreign key
III. The attribute Transaction_Date should be set to
DEFAULT
SOLUTION
CREATE TABLE transaction ( Transaction_no INT PRIMARY KEY
NOT NULL,
Product varchar(50),
Amount decimal(9,2),
Transaction_Date DateTime Default Now(),
Customer_Id INT,
CONSTRAINT FK_Customer_Id FOREIGN KEY(customer_Id)
REFERENCES customers(customer_Id)

);
Lets insert values into the
transaction table
INSERT INTO transaction (Transaction_no, Product ,
Amount, customer_Id)
values (8, "Apple", 200.20, 5),
(2, "sharwama", 2500, 2),
(3, "Diaper", 5500, 1),
(4, "Calculator", 7000, 6),
(5, "T-Shirt", 15000, 1),
(9, “Calculator", 200.20, 2);
Result of Example 2
• Result of query
Primary-Foreign Key Relationship

Transactions_Table Customers_Table
Applying a Foreign Key to a Table
that Already existed
• To apply a FK constraint to a table that already existed, use the query:

ALTER TABLE TableName

ADD CONSTRAINT constraintname FOREIGN KEY

(TargetAttribute) REFERENCES basetable
(TargetAttribute);
DROPPING a FOREIGN KEY (FK)
• To drop a FK, use the query:

• ALTER TABLE TableName

• DROP FOREIGN KEY Name_of_FK;
JOIN
• A join is a clause used to combine rows from two or
more tables based on a related column between them.
Joins can be:
• Inner join
• Left join and
• Right join
Inner join on Table customers and
Transactions
SELECT * FROM
customer INNER JOIN transactions
ON customer.customer_Id= Transaction.customer_Id;
Result from Inner Join
• Inner join
Left join on Table customer and
Transaction
SELECT * From
customer LEFT JOIN transaction
ON customer.customer_Id= transaction.customer_Id;
Result from Left Join
• Left Join
Right join on Table customer and
Transaction
SELECT * FROM
customer RIGHT JOIN transaction
ON customer.customer_Id= transaction.customer_Id;
Result from Right Join
• Right Join
More Concepts on DML
• Support the basic data manipulation operations on
the data held in the database.
• Enables users to access and manipulate data
• Goal is to provide efficient human interaction with the
system.
• Query language
Writing SQL Commands
• SQL statement consists of reserved words and user-defined words.
• Reserved words are a fixed part of SQL and must be spelt exactly as
required and cannot be split across lines.
• User-defined words are made up by user and represent names of
various database objects such as relations, columns, views.
• Most components of an SQL statement are case insensitive, except
for literal character data.
• More readable with indentation and lineation:
• Each clause should begin on a new line.
• Start of a clause should line up with start of other clauses.
• If clause has several parts, should each appear on a separate line
and be indented under start of clause.
Literals
• Literals are constants used in SQL statements.
• All non-numeric literals must be enclosed in single
quotes (e.g. ‘London’).
• All numeric literals must not be enclosed in quotes (e.g.
650.00).
SELECT Statement
SELECT [DISTINCT | ALL] {* | [columnExpression [AS
newName]] [,...] }
FROM TableName [alias] [, ...]
[WHERE condition]
[GROUP BY columnList]
[HAVING condition]
[ORDER BY columnList]
SELECT Statement
• FROM Specifies table(s) to be used.
• WHERE Filters rows.
• GROUP BY Forms groups of rows with same
column value.
• HAVING Filters groups subject to some condition.
• SELECT Specifies which columns are to appear in
output.
• ORDER BY Specifies the order of the output.
• Order of the clauses cannot be changed.
• Only SELECT and FROM are mandatory.
Example 3 All Columns, All Rows

List full details of all customers.

SELECT customer_Id, Fname, Lname, Address

FROM customers;

• Can use * as an abbreviation for ‘all columns’:

SELECT *
FROM customers;
All column, All rows from table
customers
Example 4 Specific Columns, All
Rows
Produce a list of addresses for all customers, showing
only customer_Id, Fname and address.

SELECT , customer_Id, Fname, address

FROM customers;
Example 5 Use of DISTINCT
• List the address of all everyone that is a
customer.
SELECT address
FROM customers;
Example 5 Use of DISTINCT
• Use DISTINCT to eliminate duplicates:

SELECT DISTINCT address

FROM customers;
More Practice Examples on SQL
Queries

• Our SQL queries shall be based on the relations in the

next slides
Instance of the DreamHome rental database
73
Example 8 Calculated Fields
Produce a list of monthly salaries for all staff, showing staff
number, first and last names, and salary details.

SELECT staffNo, fName, lName, salary/12

FROM Staff;

74
Example 8 Calculated Fields
• To name column, use AS clause:

SELECT staffNo, fName, lName, salary/12

AS monthlySalary
FROM Staff;

75
Example 9 Comparison Search
Condition
List all staff with a salary greater than 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff WHERE salary > 10000;

76
Example 9 Compound Comparison
Search Condition
List addresses of all branch offices in London or
Glasgow.

SELECT *
FROM Branch
WHERE city = “London” OR city =
“Glasgow”;

77
Example 10 Range Search Condition
List all staff with a salary between 20,000 and 30,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;

• BETWEEN test includes the endpoints of range.

78
Example 10 Range Search
Condition
• Also a negated version NOT BETWEEN.
• BETWEEN does not add much to SQL’s expressive
power. Could also write:

SELECT staffNo, fName, lName, position, salary

FROM Staff
WHERE salary>=20000 AND salary <= 30000;

• Useful, though, for a range of values.

79
Example 11 Set Membership
List all managers and supervisors.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);

80
Example 11 Set Membership
• There is a negated version (NOT IN).
• IN does not add much to SQL’s expressive power.
• Could have expressed this as:

SELECT staffNo, fName, lName, position

FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;

81
Example 12 Pattern Matching
Find all owners with the string ‘Glasgow’ in their address.

SELECT clientNo, fName, lName, address, telNo

FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;

82
Example 12 Pattern Matching
• SQL has two special pattern matching symbols:
• %: sequence of zero or more characters;
• _ (underscore): any single character.

• LIKE ‘%Glasgow%’ means a sequence of characters of any

length containing ‘Glasgow’.

83
Example 13 NULL Search Condition
List details of all viewings on property PG4 where a comment has
not been supplied.
• There are 2 viewings for property PG4, one with and one without
a comment.

• Have to test for null explicitly using special keyword IS NULL:

SELECT clientNo, viewDate

FROM Viewing
WHERE propertyNo = ‘PG4’ AND
comment IS NULL;

84
Example 13 NULL Search Condition

• Negated version (IS NOT NULL) can test for non-null

values.

85
Example 14 Single Column
Ordering
List salaries for all staff, arranged in descending order of
salary.

SELECT staffNo, fName, lName, salary

FROM Staff
ORDER BY salary DESC;

86
Example 14 Single Column Ordering

87
Example 15 Multiple Column
Ordering
Produce abbreviated list of properties in order of property
type.

SELECT propertyNo, type, rooms, rent

FROM PropertyForRent
ORDER BY type;

88
Example 15 Multiple Column Ordering

89
Example 15 Multiple Column Ordering

• Four flats in this list - as no minor sort key specified,

system arranges these rows in any order it chooses.

• To arrange in order of rent, specify minor order:

SELECT propertyNo, type, rooms, rent

FROM PropertyForRent
ORDER BY type, rent DESC;

90
Example 15 Multiple Column
Ordering

91
SELECT Statement - Aggregates

• ISO standard defines five aggregate functions:

COUNT returns number of values in specified column.

SUM returns sum of values in specified column.
AVG returns average of values in specified column.
MIN returns smallest value in specified column.
MAX returns largest value in specified column.

92
SELECT Statement - Aggregates

• Each operates on a single column of a table and returns a

single value.
• COUNT, MIN, and MAX apply to numeric and non-numeric
fields, but SUM and AVG may be used on numeric fields
only.

• Apart from COUNT(*), each function eliminates nulls first

and operates only on remaining non-null values.

93
SELECT Statement - Aggregates
• COUNT(*) counts all rows of a table, regardless of whether
nulls or duplicate values occur.

• Can use DISTINCT before column name to eliminate

duplicates.

• DISTINCT has no effect with MIN/MAX, but may have with

SUM/AVG.

94
SELECT Statement - Aggregates
• Aggregate functions can be used only in SELECT list and in
HAVING clause.

• If SELECT list includes an aggregate function and there is

no GROUP BY clause, SELECT list cannot reference a
column out with an aggregate function. For example, the
following is illegal:

SELECT staffNo, COUNT(salary)

FROM Staff;

95
Example 16 Use of COUNT(*)
How many properties cost more than $350 per month to
rent?

SELECT COUNT(*) AS count

FROM PropertyForRent
WHERE rent > 350;

96
Example 16 Use of COUNT(DISTINCT)
How many different properties viewed in May ‘04?

SELECT COUNT(DISTINCT propertyNo) AS count

FROM Viewing
WHERE viewDate BETWEEN ‘1-May-04’ AND ‘31-May-04’;

97
Example 16 Use of COUNT and SUM
Find number of Managers and sum of their salaries.

SELECT COUNT(staffNo) AS count,

SUM(salary) AS sum
FROM Staff
WHERE position = ‘Manager’;

98
Example 17 Use of MIN, MAX, AVG
Find minimum, maximum, and average staff salary.

SELECT MIN(salary) AS min,

MAX(salary) AS max,
AVG(salary) AS avg
FROM Staff;

99
SELECT Statement - Grouping
• Use GROUP BY clause to get sub-totals.
• SELECT and GROUP BY closely integrated: each item in
SELECT list must be single-valued per group, and SELECT
clause may only contain:

• column names
• aggregate functions
• constants
• expression involving combinations of the above.

100
SELECT Statement - Grouping
• All column names in SELECT list must appear in GROUP BY
clause unless name is used only in an aggregate function.
• If WHERE is used with GROUP BY, WHERE is applied first,
then groups are formed from remaining rows satisfying
predicate.
• ISO considers two nulls to be equal for purposes of GROUP
BY.

101
Example 18 Use of GROUP BY
Find number of staff in each branch and their total
salaries.

SELECT branchNo,
COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;

102
Example 18 Use of GROUP BY

103
Restricted Groupings – HAVING clause
• HAVING clause is designed for use with GROUP BY to
restrict groups that appear in final result table.
• Similar to WHERE, but WHERE filters individual rows
whereas HAVING filters groups.
• Column names in HAVING clause must also appear in the
GROUP BY list or be contained within an aggregate
function.

104
Example 19 Use of HAVING
For each branch with more than 1 member of staff, find number of
staff in each branch and sum of their salaries.

SELECT branchNo,
COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;

105
Example 19 Use of HAVING

106
Subqueries
• Some SQL statements can have a SELECT embedded
within them.
• A subselect can be used in WHERE and HAVING clauses of
an outer SELECT, where it is called a subquery or nested
query.
• Subselects may also appear in INSERT, UPDATE, and
DELETE statements.

107
Example 20 Subquery with Equality
List staff who work in branch at ‘163 Main St’.

SELECT staffNo, fName, lName, position

FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);

108
Example 20 Subquery with Equality
• Inner SELECT finds branch number for branch at ‘163
Main St’ (‘B003’).

• Outer SELECT then retrieves details of all staff who work

at this branch.
• Outer SELECT then becomes:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’;
109
Example 20 Subquery with Equality

110
Example 21 Subquery with Aggregate
List all staff whose salary is greater than the
average salary, and show by how much.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);

111
Example 21 Subquery with
Aggregate
• Cannot write ‘WHERE salary > AVG(salary)’
• Instead, use subquery to find average salary (17000), and
then use outer SELECT to find those staff with salary
greater than this:

SELECT staffNo, fName, lName, position,

salary – 17000 As salDiff
FROM Staff
WHERE salary > 17000;

112
Example 21 Subquery with
Aggregate

113
Subquery Rules
• ORDER BY clause may not be used in a subquery
(although it may be used in outermost SELECT).

• Subquery SELECT list must consist of a single column

name or expression, except for subqueries that use
EXISTS.
• When subquery is an operand in a comparison, subquery
must appear on right-hand side.

114
Example 22 Nested subquery: use
of IN
List properties handled by staff at ‘163 Main St’.
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
115
Example 22 Nested subquery: use
of IN

116
Create Horizontal View
Create view so that manager at branch B003 can
only see details for staff who work in his or her office.
CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branchNo = ‘B003’;

117
Create Vertical View
Create view of staff details at branch B003 excluding salaries.

CREATE VIEW Staff3

AS SELECT staffNo, fName, lName, position, sex
FROM Staff
WHERE branchNo = ‘B003’;

118
Class Activity 2 (Database Tables)

Customer_t Orderline_t

119
Class Activity 2 (Database Tables) cont.

Product _t
Order_t

120
Question 1.

• Which products have a standard price of less than 275.

12/28/2024
Solution

12/28/2024
Question 2

• What is the address of the customer named Home

Furnishings. Use an alias NAME for customer.

12/28/2024
Solution
SELECT CUSTOMER_NAME AS NAME ,
CUSTOMER_ADDRESS
FROM CUSTOMER_t
WHERE CUSTOMER_NAME =‘Home
Furnishings’;

12/28/2024
Question 3

• List the unit price, product name and product ID for all
products in the product table.

12/28/2024
Solution

12/28/2024
Question 4
• What is the average standard price for product in inventory.

12/28/2024
Question 5

• How many different items were ordered on order

number 1004

12/28/2024
Question 5

• Which orders have been placed since 10/24/2006

12/28/2024
Question 6

• What furniture does Pine Valley carry that isn’t made of

cherry?

12/28/2024
Question 7

• List products, finish, and unit price for all

desks and all tables that cost more than
300 in the PRODUCT view.

12/28/2024
Solution

12/28/2024
Question 8

• Which products in the PRODUCT view

have a standard price between 200 and
300.

12/28/2024
Question 9
• Count the number of customers with address in each state to
which we ship

12/28/2024
Question 10
• Find only states with more than one customer

SELECT State, COUNT(STATE) AS StateNumber

FROM Customer_t
GROUP BY State
Having COUNT(State)>1

State StateNumber
CA 2
FL 3
NJ 2

12/28/2024
Question 11
• List the product finish and average standard price for cherry,
natural ash, natural maple and white ash, where the average
standard price is less than 750.

12/28/2024
Exercises

1. Write a database description for each of the relations

given. Assume the following attribute data types.
• StudentID(integer, primary key)
• StudentName(25 characters)
• FacultyID(integer, primary key)
• FacultyName(25 characters)
• CourseID(8 characters, primary key)
• CourseName(15 characters)
• DateQualified(date)
• SectionNo(integer, primary key)
• Semester(7 characters)
137
Exercises cont.

Diagram for Exercise 1

138
Lecture Reference
• Ramakrishnan, R., Gehrke, J.: Database Management Systems.
USA: McGraw Hill Companies (2000)
• Elmasri, R., & Navathe, S. (2000). Fundamentals of database
systems (3rd ed.). Reading, Mass.: Addison-Wesley.
• Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2005). Modern
database management. Upper Saddle River, N.J: Pearson/ Prentice
Hall.
• Connolly, T. M., & Begg, C. E. (2002). Database systems: A
practical approach to design, implementation, and management.
Harlow, England: Addison-Wesley
• http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/solution
s/ans3ed-oddonly.pdf
• https://gyires.inf.unideb.hu/GyBITT/03/ 139
SQL Server Data Types

String Data Types

https://www.w3schools.com/sql/sql_datatypes.asp

140
SQL Server Data Types cont.

Number Data Types

https://www.w3schools.com/sql/sql_datatypes.asp

141
SQL Server Data Types cont.

https://www.w3schools.com/sql/sql_datatypes.asp Number Data Types

142
SQL Server Data Types cont.

Date Data Type

https://www.w3schools.com/sql/sql_datatypes.asp

143
SQL Server Data Types cont.

https://www.w3schools.com/sql/sql_datatypes.asp Other Data Types

Back

144