Download STRUCTURED QUERY LANGUAGE BASICS and more Lecture notes Database Programming in PDF only on Docsity!
Lecture Notes on SQL Commands Objectives of the Lecture By the end of this lecture, students should be able to: Understand the different sublanguages of SQL and their roles in database management. Differentiate between DDL, DML, DQL, TCL, and DCL. Understand different data types used in MYSQL Apply SQL commands to create, modify, retrieve, and manage data in a MySQL database. Set constraints on database objects Understand different types of table Joins Use SQL views for improved data management and security. Implement transaction control (TCL) and access control mechanisms using Data Control Language (DCL).
SQL Overview SQL is a standard language for accessing and manipulating Relational Databases
SQL stands for Structured Query Language
SQL is an ANSI (American National Standards Institute) standard RDBMS stands for Relational Database Management System. The data in RDBMS is stored in database objects called tables. To access data in a database, you need to use SQL statements and operators. SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: Data query Language (DQL), Data Definition Language (DDL), Data Control Language (DCL), and Data Manipulation Language (DML).
- Data query Language (DQL) Use SELECT statements to retrieve data from one or more tables
- Data manipulation Language (DML) Use INSERT, UPDATE AND DELETE to modify rows in a table
- Data definition Language (DDL) Use CREATE, ALTER, RENAME, TRUNCATE, DROP to define and manage database objects
- Data control Language (DCL) Use GRANT and REVOKE to control user access and privileges
- Transaction control (TCL) Use COMMIT, ROLLBACK, and SAVEPOINT for handling transactions Data Definition Language (DDL) DDL commands are used to define and manage database objects such as tables, views, indexes, and constraints. Key DDL Commands
- CREATE – Creates a new database object.
- ALTER – Modifies an existing database object.
- DROP – Deletes an entire database object.
- TRUNCATE – Removes all records from a table but keeps the structure.
- RENAME – Renames an existing database object.
CREATE statement
- Create database CREATE DATABASE Agribi;
- Select database USE Agribi;
- Creating the table CREATE TABLE customer ( customer_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100) UNIQUE, phone VARCHAR(20), city VARCHAR(50) ); INSERT Statement Adds new records Inserting records into above table INSERT INTO customer (name, email, phone, city) VALUES ('John Doe', '[email protected]', '1234567890', 'New York'); SELECT Statement Display all table records SELECT * FROM customer; UPDATE Statement Modifies existing records Update phone number for customer whose id is 1 UPDATE customer SET phone = '9876543210' WHERE customer_id = 1; The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city. Example
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1; UPDATE Multiple Records It is the WHERE clause that determines how many records that will be updated. The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico": Example UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico'; Update Warning! Be careful when updating records. If you omit the WHERE clause, ALL records will be updated! Example UPDATE Customers SET ContactName='Juan'; DELETE Statement Removes existing records from a table Deleting a customer record DELETE FROM customer WHERE customer_id = 1; TRUNCATE TABLE Statement Is used to delete the data inside a table, but not the table itself. Truncating the customer table TRUNCATE TABLE customer; SHOW Statement
- Display all databases available in the system SHOW DATABASES;
- Display all tables in Agribi database SHOW tables;
- View table structure
Naming primary key constraint To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( ID int NOT NULL, FirstName varchar(255) NOT NULL, LastName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID, LastName) ); Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName). SQL PRIMARY KEY on ALTER TABLE To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD PRIMARY KEY (ID); To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created). DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL: ALTER TABLE Persons DROP PRIMARY KEY; SQL VIEW
A view is a virtual table that is based on the result of a SQL query. It does not store data itself but provides a way to simplify complex queries by presenting data from one or more tables in a structured manner. Key Features of a MySQL View:
- Virtual Table: It behaves like a table but does not store data.
- Simplifies Queries: It helps in simplifying complex SQL queries by encapsulating them.
- Security and Access Control: Views can be used to restrict access to specific columns or rows of a table.
- Data Abstraction: Users can interact with views without needing to understand the underlying table structures. Creating a View: You can create a view using the CREATE VIEW statement: CREATE VIEW view_name AS SELECT column1, column FROM table_name WHERE condition; Example: If you have a customers table and want a view that only shows customers from Uganda: CREATE VIEW ugandan_customers AS SELECT id, name, email FROM customers WHERE country = 'Uganda'; Using a View: You can query a view just like a table: SELECT * FROM ugandan_customers; Updating a View: You can modify an existing view using: ALTER VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition; Dropping a View: To remove a view:
SELECT DISTINCT Examples The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table: Example SELECT DISTINCT Country FROM Customers; The following SQL statement lists the number of different (distinct) customer countries: Example SELECT COUNT(DISTINCT Country) FROM Customers; SQL Aliases SQL aliases are used to give a table , or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.
- Alias Column Syntax SELECT column_name AS alias_name FROM table_name;
- Alias Table Syntax SELECT column_name(s) FROM table_name AS alias_name; SQL NULL Values A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Note: It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation! How to Test for NULL Values? It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead. IS NULL Syntax SELECT column_names FROM table_name
WHERE column_name IS NULL; IS NOT NULL Syntax SELECT column_names FROM table_name WHERE column_name IS NOT NULL; The IS NULL Operator The following SQL statement uses the IS NULL operator to list all persons that have no address: SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NULL; Always use IS NULL to look for NULL values. The IS NOT NULL Operator The following SQL statement uses the IS NOT NULL operator to list all persons that do have an address: SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NOT NULL; The SQL MIN() and MAX() Functions The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column. MIN() Syntax SELECT MIN(column_name) FROM table_name WHERE condition; MAX() Syntax SELECT MAX(column_name) FROM table_name WHERE condition; MIN() Example The following SQL statement finds the price of the cheapest product: Example SELECT MIN(Price) AS SmallestPrice FROM Products;
FROM Products; SUM() Example The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table: Example SELECT SUM(Quantity) FROM OrderDetails; SQL FOREIGN KEY INTEGRITY CONSTRAINT A foreign key is used to enforce referential integrity between two tables. Foreign Key Integrity Constraint Between orders and customers Tables in MySQL. In this case, the orders table should have a foreign key referencing the customers table to ensure that an order cannot be placed without a valid customer.
- Creating the customers Table (Parent Table) This table contains customer details and serves as the referenced table. CREATE TABLE customers ( customer_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL );
- Creating the orders Table (Child Table) This table contains order details and references customer_id from the customers table. CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, order_date DATE NOT NULL, customer_id INT, amount DECIMAL(10,2) NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE ); Explanation of Foreign Key Constraint
customer_id in orders is a foreign key that references customer_id in customers. ON DELETE CASCADE → If a customer is deleted, all their orders will also be deleted. ON UPDATE CASCADE → If a customer's customer_id changes, it will update automatically in orders. Testing Foreign Key Constraint Inserting Data INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]'); INSERT INTO orders (order_date, customer_id, amount) VALUES ('2024- 02 - 21', 1, 150.00); Checking Data SELECT * FROM customers; SELECT * FROM orders; Deleting a Customer (Cascade Effect) DELETE FROM customers WHERE customer_id = 1; This will automatically delete the related orders. Handling Foreign Key Violations If you try to insert an order with a customer_id that doesn’t exist, it will fail: INSERT INTO orders (order_date, customer_id, amount) VALUES ('2024- 02 - 21', 999, 200.00); Error: Cannot add or update a child row: a foreign key constraint fails SQL JOINS A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Types of SQL Joins Here are the different types of the JOINs in SQL:
- (INNER) JOIN : Returns records that have matching values in both tables
- LEFT (OUTER) JOIN : Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN : Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN : Return all records when there is a match in either left or right table
table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables: Example SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown! JOIN Three Tables The following SQL statement selects all orders with customer and shipper information: Example SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID); SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. LEFT JOIN Syntax SELECT column_name(s) FROM table LEFT JOIN table2 ON table1.column_name = table2.column_name; Example Recall the "Customers" table and the "Orders" table used under INNER JOIN above: SQL LEFT JOIN Example The following SQL statement will select all customers, and any orders they might have: Example
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders). SQL RIGHT JOIN Keyword The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. RIGHT JOIN Syntax SELECT column_name(s) FROM table RIGHT JOIN table2 ON table1.column_name = table2.column_name; Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN
SQL Self JOIN A self JOIN is a regular join, but the table is joined with itself. Self JOIN Syntax SELECT column_name(s) FROM table1 T1, table1 T WHERE condition SQL Self JOIN Example The following SQL statement matches customers that are from the same city: Example SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City; The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements.
- Each SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in each SELECT statement must also be in the same order UNION Syntax SELECT column_name(s) FROM table UNION SELECT column_name(s) FROM table2; UNION ALL Syntax The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL: SELECT column_name(s) FROM table UNION ALL SELECT column_name(s) FROM table2; Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.
SQL UNION Example The following SQL statement selects all the different cities (only distinct values) from "Customers" and "Suppliers": Example SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City; Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values! SQL UNION ALL Example The following SQL statement selects all cities (duplicate values also) from "Customers" and "Suppliers": Example SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers
