Database Fundamentals
Database Fundamentals
SQL Introduction – Data Manipulation Language
1
SQL Commands Overview – Data Manipulation
Key Data Manipulation SQL commands:
INSERT INTO – Creates new tuple(s) in a table in the database
DELETE FROM – Permanently removes tuple(s)
UPDATE – Modifies existing tuple(s) in a table in the database
SELECT – Retrieves one or more tuples of data (a Query)
What’s this for?
So we have created a set of tables.
How do we actually put data in our database?
How do we modify data that is already in our database?
How do we answer questions using our database data?
Example Database Design
Students(StudentID, Name, Email)
PK(StudentID)
CK(Email)
Courses(CourseID, Name, StudentNo)
PK(CourseID)
CK(Name)
Enrolments(StudentID, CourseID, DateEnrolled, Mark)
PK(StudentID, CourseID, DateEnrolled)
FK(StudentID) -> Students(StudentID)
FK(CourseID) -> Courses(CourseID)
Database Implementation
Possible Conceptual Designs
Example Database Design
CREATE TABLE Students (
StudentID int,
Name varchar(100) NOT NULL,
Email varchar(100) NOT NULL,
CONSTRAINT StudentPK PRIMARY KEY(StudentID),
CONSTRAINT UniqueEmail UNIQUE(Email)
);
CREATE TABLE Courses (
CourseID int,
Name varchar(100) NOT NULL,
StudentNo int DEFAULT 50,
CONSTRAINT CoursePK PRIMARY KEY(CourseID),
CONSTRAINT UniqueName UNIQUE(Name),
CONSTRAINT maxStudents CHECK(StudentNo BETWEEN 5 AND 150)
);
Students(StudentID, Name, Email)
PK(StudentID)
CK(Email)
Courses(CourseID, Name, StudentNo)
PK(CourseID)
CK(Name)
Example Database Design
CREATE TABLE Enrolments (
CourseID int NOT NULL,
StudentID int NOT NULL,
DateEnrolled date DEFAULT getDate(),
Mark int CHECK(Mark BETWEEN 0 AND 100),
CONSTRAINT EnrolmentPK PRIMARY KEY(CourseID, StudentID, DateEnrolled),
CONSTRAINT theStudent FOREIGN KEY(StudentID) REFERENCES Students(StudentID),
CONSTRAINT theCourse FOREIGN KEY(CourseID) REFERENCES Courses(CourseID)
);
Enrolments(StudentID, CourseID, DateEnrolled, Mark)
PK(StudentID, CourseID, DateEnrolled)
FK(StudentID) -> Students(StudentID)
FK(CourseID) -> Courses(CourseID)
INSERT INTO
SQL Commands Overview – INSERT INTO
Used to populate with data – your newly created database!
String data (text) must be wrapped in single ‘quotations’
Same applies to date strings: ‘01/03/2014’
Syntax:
INSERT INTO
Inserts new values in the default order of the columns in the database table
INSERT INTO
Inserts the values in a different order to the default order of the columns in the database table
Examples:
INSERT INTO Student VALUES (‘50011’, ‘Barry’);
INSERT INTO Student (StudentName, StudentID) VALUES (‘Stacy’, ‘50022’);
Be careful using this method if the order of columns changes.
Note the different order of StudentName vs StudentID. If the order is not specified, you must insert the values in order of the table column names (Left Right)
Order specified in this statement
The SAME order of attributes when the table was created!!
SQL – INSERT new records
Syntax
INSERT INTO TableName [(AttributeList)]
Using values
INSERT INTO Departments (DeptName, City)
VALUES (‘Development’,’San Jose’)
Using a Sub-Query
INSERT INTO Student
SELECT StudentID, Email, Name
FROM otherStudentTable
WHERE Name IS NOT NULL
INSERT INTO Student (StudentID, StudentName)
SELECT StudentID, StudentName
FROM otherTable
WHERE Name IS NOT NULL;
Best Approach
Attribute List
Attribute Values
SQL Commands Overview – INSERT INTO
The order of the attributes (if present) are important (first value for the first attribute, and so on)
If the AttributeList is omitted, all the values must be supplied in the same order used when the table structure was created
CREATE Table (Col1, Col2, Col3)
INSERT INTO Table VALUES (val1, val2, val3)
Where Val1 is for Col1, Val2 for Col2 …
If the AttributeList does not contain all the attributes, the remaining attributes are assigned their DEFAULT values (or NULL if no default has been defined)
INSERT INTO Table(col1, col2) VALUES (val1, val2)
The new row inserted will be (val1, val2, defaultVal/NULL)
Assuming the table has 3 columns
This WILL throw an ERROR if the attribute is set as NOT NULL and no DEFAULT has been specified
SQL Commands Overview – INSERT INTO
CREATE TABLE Departments(
Dept char(4),
DeptName varchar(100) NOT NULL,
CONSTRAINT deptPk PRIMARY KEY(Dept),
CONSTRAINT uniqueName UNIQUE(DeptName)
);
CREATE TABLE Employees (
RegNo char(6),
FirstName char(20) NOT NULL,
Surname char(20) NOT NULL,
Dept char(4),
Salary decimal(9) DEFAULT 0,
Bdate date,
CONSTRAINT employeePk PRIMARY KEY (RegNo),
CONSTRAINT uniueName UNIQUE (Surname, FirstName),
CONSTRAINT departmentsFk FOREIGN KEY (Dept) REFERENCES Departments(Dept)
);
Does the order matter??
Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)
Departments ( DeptName, Address, City )
SQL Commands Overview – INSERT INTO
SQL> SELECT * FROM employees;
REGNO FIRSTNAME SURNAME DEPT SALARY BDATE
—— ———— ———- —— ———- ———–
E001 John Smiths CIS 40000 23-JAN-1980
E002 Peter Smiths CIS 0 NULL
INSERT INTO Departments VALUES (‘CIS’, ‘Computer and Information Science’);
INSERT INTO Employees VALUES (‘E001’, ‘John’, ‘Smiths’, ‘CIS’, 40000, ’23-JAN-1980′);
/* The value order MUST match the attribute order specified when the table was created (previous slide!) */
/* Dates with the month in text must be encased in single quotes if the Month contains text */
INSERT INTO Employees (Dept, Regno, Firstname, Surname)
VALUES (‘CIS’,’E002′,’Peter’,’Smiths’);
/* The value order MUST match the attribute order of the insert query */
In both cases:
values for PK and unique constraints must be satisfied
values must satisfy FK and other constraints
Best Approach
Delete From
SQL Commands Overview – DELETE FROM
The DELETE FROM statement removes all the tuples that satisfy the condition from a given table
The removal may result in deletions from other tables if a FOREIGN KEY constraint with CASECADE ON DELETE has been used
If the WHERE clause is omitted, DELETE FROM removes ALL tuples from the table:
DELETE FROM Departments; /* removes ALL departments from the table */
DELETE FROM Departments WHERE DeptName = ‘ITMS’ ;
/* removes ALL departments from the table with the name ITMS */
SQL Commands Overview – DELETE FROM
The WHERE clause dictates which rows of data should be deleted
Each row is compared to the WHERE clause and if it returns TRUE, the row is deleted
If you want to delete a specific record, what should the WHERE clause contain?
DELETE FROM Departments WHERE ??
SQL Commands Overview – DELETE FROM
Delete tuples
DELETE FROM Employees;
Deletes ALL tuples from the table, but not the table/schema itself
Delete from employees where regno = ‘E001’;
Deletes matching tuples (one tuple is deleted in previous example)
Delete the tuples and the schema (remove the table)
DROP TABLE Departments
Deletes all employees and Employee table followed by departments
Can the opposite be done?
If Employees contains a reference (FK) to Departments, what will happen?
DELETE FROM Employees;
DELETE FROM employees WHERE regno = ‘E001’;
DROP TABLE Employees;
DROP TABLE Departments;
Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)
Departments ( DeptName, Address, City )
16
SQL Commands Overview – DELETE FROM
Syntax
DELETE FROM TableName [WHERE Condition]
Remove employee Neri Jackson
Remove departments without employees
DELETE FROM Employees
WHERE FirstName = ‘Neri’
AND Surname = ‘Jackson’
DELETE FROM Departments
WHERE DeptName NOT IN (
SELECT Dept FROM Employees
);
Update
SQL Commands Overview – UPDATE tableName SET
Syntax
UPDATE TableName
SET Attribute=
{, SET Attribute=
[WHERE Condition]
Examples
UPDATE Employees
SET Salary = Salary + 5
WHERE RegNo = ‘M2047′
UPDATE Employees
SET Salary = Salary * 2.0
WHERE Dept = ‘Middle Management’
Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)
Departments ( DeptName, Address, City )
Note correction
SQL Commands Overview – UPDATE tableName SET
Since the language is set-oriented, the order of the statements can be important
If the statements are issued in this order, some employees may get a double raise!
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Salary < 30
UPDATE Employees
SET Salary = Salary * 1.15
WHERE Salary >= 30
Query 1
Query 2
Yay!
Remember SQL can be Fun!!
Table Creation, Data Manipulation. Query writing
SQL Introduction – Query Writing (SELECT)
22
So What’s This For?
You have got your tables/Database
You have got data in your database
Now lets start asking questions about our data
SQL as a query language
Remember – SQL expresses queries in a declarative way
Queries focus on the result, not how to obtain it
Queries are translated by the query optimiser into the procedural language internal to the DBMS
The DBMS Optimises the query and executes it against the database
The programmer should focus on readability, not on efficiency
In most cases! – only return the results you need
24
Select
SQL Commands Overview – SELECT x,y,z FROM
SELECT is used to retrieve data from the database
Remember SQL is Declarative
You don’t tell the DBMS how to get the data only about the data you want
Syntax:
SELECT
Also called a SFW query
Examples:
Query Purpose
SELECT StudentID, StudentName FROM Student; Select specific columns from all tuples from Student
SELECT * FROM Student; Select all columns and all tuples from Student (display all records)
SELECT Student.* FROM Student; Select all columns and all tuples from Student specifically
SELECT * FROM Student WHERE StudentName = ‘Barry Williams’ Select all columns and all tuples with student name “Barry Williams”
SQL Commands Overview – SELECT x,y,z FROM
Syntax (abbreviated)
SELECT AttrExpr [[as] Alias {, AttrExpr [[as] Alias]}
FROM