This document provides an overview of basic SQL commands including INSERT, UPDATE, DELETE, and SELECT. It explains that SQL is used to define commands to create databases and tables, perform basic data management, and query information. The SELECT command allows users to retrieve data and supports various operators, aggregate functions, and clauses like GROUP BY and HAVING to filter and organize query results. Other DML commands like INSERT add new rows, UPDATE modifies existing rows, and DELETE removes rows from tables.
This document provides an overview of basic SQL commands including INSERT, UPDATE, DELETE, and SELECT. It explains that SQL is used to define commands to create databases and tables, perform basic data management, and query information. The SELECT command allows users to retrieve data and supports various operators, aggregate functions, and clauses like GROUP BY and HAVING to filter and organize query results. Other DML commands like INSERT add new rows, UPDATE modifies existing rows, and DELETE removes rows from tables.
This document provides an overview of basic SQL commands including INSERT, UPDATE, DELETE, and SELECT. It explains that SQL is used to define commands to create databases and tables, perform basic data management, and query information. The SELECT command allows users to retrieve data and supports various operators, aggregate functions, and clauses like GROUP BY and HAVING to filter and organize query results. Other DML commands like INSERT add new rows, UPDATE modifies existing rows, and DELETE removes rows from tables.
This document provides an overview of basic SQL commands including INSERT, UPDATE, DELETE, and SELECT. It explains that SQL is used to define commands to create databases and tables, perform basic data management, and query information. The SELECT command allows users to retrieve data and supports various operators, aggregate functions, and clauses like GROUP BY and HAVING to filter and organize query results. Other DML commands like INSERT add new rows, UPDATE modifies existing rows, and DELETE removes rows from tables.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
You are on page 1/ 29
Basic SQL
ITCS 201 Web Programming Part II
Outline for Today
What is SQL? SQL Category Basic DML Commands
INSERT UPDATE DELETE SELECT
Page 2
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
What is SQL? SQL Structured Query Language Language that defines commands for user to Create database and table structure
Basic data management
Query useful information Not case-sensitive Non procedural language : users specify what must be done, NOT how it is to be done
Page 3
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SQL Categories Two broad type 1. Data Definition Language (DDL) NOT Study in this course
Commands used to create database objects including
Database Table/Attributes Keys etc.
2. Data Manipulation Language (DML)
Commands used to insert, update, delete, and retrieve data within tables.
Page 4
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
Basic DML Commands
Commands INSERT UPDATE DELETE SELECT
Description Insert row(s) into a table Modify an attributes value in one or more tables rows Delete one or more rows from a table Select attributes from rows in one or more tables
WHERE GROUP BY HAVING ORDER BY
Restrict the selection of rows based on condition expression
Group the selected rows based on one or more attributes Restrict the selection of group rows based on a condition Order the selected rows based on one or more attributes
Page 5
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
Commands Comparison Operators =,<,>,<=,=>,<> Logical Operators AND/OR/NOT Special Operators IS NULL LIKE
Description Used in conditional expression
Used in conditional expression
Check whether an attribute value is null Check whether an attribute value matches a given string pattern
DISTINCT Aggregate Functions COUNT
Limit values to unique values
Returns the number of rows with non-null values for a given column
MIN MAX SUM AVG Page 6
Return the minimum attribute value found in a given column
Return the maximum attribute value found in a given column Return the sum of all values for a given column Return the average of all values for a given column ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
INSERT Command Use INSERT command to enter data into a table Basic syntax: INSERT INTO tablename VALUES( value1, value2, , valueN)
Note: Be careful with data type and allow-null attribute
INSERT INTO computers VALUES( '37', Lenovo Y550');
Page 7
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
INSERT Command (cont.)
Special case of INSERT 1. Insert rows with null attributes If we do not know some values of attribute, we have to leave them as null To enter a null, use following syntax INSERT INTO tablename VALUES(value1, NULL,,valueN)
Note: You can insert NULL only the allow-null attribute
INSERT INTO inventory VALUES( '57', '2010-04-23', 37', 2', NULL); Page 8 ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
INSERT Command (cont.)
2. Insert rows with only required attributes Rather than declaring each attribute as NULL, indicate just only the required value
To insert only some require attribute (e.g. col1, col2, , colN), use following syntax INSERT INTO tablename(col1,col2,...,colN) VALUES(value1,value2,,valueN)
INSERT INTO inventory (inventoryID, dateAcquired, computerID, employeeID) VALUES( '57', '2010-04-23', 37', 2'); Page 9 ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
UPDATE Command Use UPDATE command to modify data in a table Basic syntax: UPDATE tablename SET col1 = expression1, col2 = expression2, colN = expressionN [WHERE conditionList]
Note: Be careful about WHERE clause, there could be a huge problem if you forget to insert WHERE condition UPDATE inventory SET dateAcquired = 2010-08-22, comments = go go go WHERE inventoryID = 57; Page 10 ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
DELETE Command Use DELETE command to delete a table row Basic syntax: DELETE FROM tablename [WHERE conditionList]
Note: Be careful about WHERE clause, there could be a huge problem if you forget to insert WHERE condition DELETE FROM inventory WHERE inventoryID = 57;
Page 11
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Use SELECT command to query partial table contents by placing conditions we want specified in WHERE clause (optional) Basic Syntax: SELECT columnList FROM tablename [ WHERE conditionList ]
The columnList specifies the selected attribute we want, if we want all attributes in that table, simply use * SELECT * FROM employee Page 12
SELECT employeeID, firstName FROM employee
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command (cont.)
Using a conditional expression in SELECT command SELECT * FROM employees WHERE lastName = 'Mouse' SELECT * FROM employees WHERE employeesID = 3
Page 13
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Logical Operators
Use Logical Operators in SELECT command to connect multiple conditional expressions AND / OR / NOT SELECT * FROM employees WHERE lastName = 'Mouse' AND firstName = 'Mickey'; SELECT * FROM employees WHERE (lastName = 'Mouse' OR lastName = 'Jetson') AND NOT ( firstName = 'Mickey');
Page 14
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Comparison Operators
Use Comparison Operators in SELECT command =,<,>,<=,=>,<> SELECT * FROM employees WHERE employeeID > 10 SELECT * FROM employees WHERE employeeID > 10 AND lastName <> Jetson
Page 15
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Special Operators: IS NULL
Use Special Operators in SELECT command 1. IS NULL Operator Use to check for a null attribute value SELECT inventoryID, comments FROM inventory WHERE inventoryID >50 AND comments IS NOT NULL
SELECT inventoryID, comments FROM inventory WHERE comments IS NULL
Page 16
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Special Operators: LIKE
2. LIKE Operator
Use with wildcards to find pattern within string attribute
% means any and all following character are eligible P% include Paul, Peter, Pat Pa% include Paul, Pat, Parrot _ means any one character may be substituted 13_11 include 13A11, 13411 _a_ include Rat, Van , Tap
Page 17
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Special Operators: LIKE
2. LIKE Operator (cont) SELECT * FROM `computers` WHERE computerDescription LIKE 'DELL%' SELECT * FROM `employees` WHERE lastName LIKE '_o_'
Page 18
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Special Operators: DISTINCT
3. DISTINCT Operator Use DISTINCT operator to produce a list of unique attribute values
SELECT DISTINCT computerID FROM inventory
Page 19
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Aggregate Function: COUNT
Use Aggregate Functions to perform mathematical summary in SELECT command 1. COUNT Function
Use COUNT function to count the number of non-null values of an attribute
SELECT COUNT( comments ) FROM inventory WHERE 1
SELECT COUNT( * ) FROM inventory WHERE computerID =1
(there are totally 26 rows)
Page 20 ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Aggregate Function: MIN/ MAX
2. MIN/MAX Function Use MIN function to find the lowest value of a given attribute Use MAX function to find the highest value of a given attribute SELECT MAX( computerID ) FROM computers
SELECT MIN( computerID ) FROM computers
Page 21
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Aggregate Function: SUM
3. SUM Function Use SUM function to compute a total sum for any specific attribute SELECT SUM(computerID) FROM inventory
Page 22
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Aggregate Function: AVG
4. AVG Function Use AVG function to compute an average value for any specific attribute SELECT AVG(computerID) FROM inventory
Page 23
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command GROUP BY
Use GROUP BY when you have other attribute columns combined with aggregate functions in SELECT command Basic Syntax: SELECT columnList FROM tablename [ WHERE conditionList ] [ GROUP BY columnList ]
SELECT employeeID, COUNT( computerID ) FROM `inventory` GROUP BY employeeID
Page 24 ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command -- HAVING
Use HAVING clause when you want to specify condition of GROUP BY clause in SELECT command Basic Syntax: SELECT columnList FROM tablename [ WHERE conditionList ] [ GROUP BY columnList ] [ HAVING conditionList ] SELECT employeeID, COUNT( computerID ) FROM `inventory` GROUP BY employeeID HAVING count(computerID)>1 Page 25 ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command ORDER BY
Use ORDER BY in SELECT command to sort values in a given attribute Basic Syntax: SELECT columnList FROM tablename [ WHERE conditionList ] [ GROUP BY columnList ] [ HAVING conditionList ] [ ORDER BY columnList [ASC | DESC]]
Page 26
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command ORDER BY
SELECT inventoryID, employeeID, comments FROM `inventory` WHERE inventoryID >40 ORDER BY employeeID SELECT inventoryID, employeeID, comments FROM `inventory` WHERE inventoryID >40 ORDER BY employeeID DESC
Page 27
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Joining Tables
Perform when data are retrieved from more than one tables Normally compose of an equality comparison (=) of FK and PK of related tables. SELECT * FROM inventory, computers WHERE inventoryID >40 AND inventory.computerID = computers.computerID
Page 28
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
SELECT Command Join Tables : Alias
Use Alias to identify the source table from which data are taken when join is performed SELECT e.firstName, e.lastName, COUNT( * ) AS 'number of computer' FROM inventory AS i, employees AS e WHERE e.employeeID = i.employeeID GROUP BY e.firstName, e.lastName HAVING COUNT( computerID ) >1 ORDER BY COUNT( * ) DESC
Page 29
ITCS201 Web Programming, The Faculty of Information and Communication Technology, Mahidol University
