sql.pdf

Using Basic Structured Query Language Learning outcomes: LO 1: Write an SQL statement to retrieve and sort data LO 2: Write SQL statements that use functions LO 3: Write SQL statements that use aggregation and filtering LO 4: Write and execute SQL sub-queries SQL Overview ✓SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. ✓SQL is the standard language for Relation Database System. ✓All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language. Why SQL?  Allows users to access data in relational database management systems.  Allows users to describe the data  Allows users to define the data in database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers.  Allows users to create and drop databases and tables Allows users to create view, stored procedure, functions in a database.  Allows users to set permissions on tables, procedures and views SQL is followed by a unique set of rules and guidelines called Syntax. All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;) SQL is case insensitive, which means SELECT and select have same meaning in SQL statements. SQL Process: ✓When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. ✓There are various components included in the process. ✓These components are Query Dispatcher, Optimization Engines, Classic Query Engine and SQL Query Engine, etc. ✓Classic query engine handles all non-SQL queries, but SQL query engine won't handle logical files. Following is a simple diagram showing SQL Architecture: Types of SQL Language Data Manipulation Language (DML), which retrieves or modifies data Data Definition Language (DDL), which defines the structure of the data Data Control Language (DCL), which defines the privileges granted to database users. Group desiccation including presentation What are the languages in SQL list the commands and their task based on the lesson that you learn on UC3 ✓SQL is a programming language for Relational Databases. ✓SQL comprises both data definition and data manipulation languages. ✓Using the data definition properties of SQL, one can design and modify database schema, whereas data manipulation properties allows SQL to store and retrieve data from database. Data Definition Language ✓SQL uses the following set of commands to define database schema 1. CREATE Creates new databases, tables and views from RDBMS. For example − ▪Create database MPTC; ▪Create table Course; ▪Create view for students; 2. DROP Drops commands, views, tables, and databases from RDBMS. Drop object_type object_name; For example− ▪ Drop database MPTC; ▪ Drop table course; ▪ Drop view for_students; 3. ALTER Modifies database schema. Alter object_type object_name parameters; For example− ▪ Alter table article add course_Name varchar; ✓ This command adds an attribute in the relation Course with the name course_Name of string type. Data Manipulation Language ✓SQL is equipped with data manipulation language (DML). ✓DML modifies the database instance by inserting, updating and deleting its data. ✓DML is responsible for all forms data modification in a database. ✓SQL contains the following set of commands in its DML section − SELECT/FROM/WHERE INSERT INTO/VALUES UPDATE/SET/WHERE DELETE FROM/WHERE ✓These basic constructs allow database programmers and users to enter data and information into the database and retrieve efficiently using a number of filter options. SELECT/FROM/WHERE SELECT − It selects the attributes based on the condition described by WHERE clause. FROM − This clause takes a relation name as an argument from which attributes are to be selected/projected. In case more than one relation names are given, this clause corresponds to Cartesian product. WHERE − This clause defines predicate or conditions, which must match in order to qualify the attributes to be projected. For example − ▪Select author_name From book_author Where age > 50; ✓This command will yield the names of authors from the relation book_author whose age is greater than Syntax− INSERT INTO table (column1 [, column2, column3... ]) VALUES (value1 [, value2, value3... ]) or INSERT INTO table VALUES (value1, [value2,... ]) UPDATE/SET/WHERE This command is used for updating or modifying the values of columns in a table (relation). Syntax − UPDATE table_name SET column_name = value [, column_name = value...] [WHERE condition] For example − UPDATE Course SET Author="webmaster" WHERE Author="anonymous"; DELETE/FROM/WHERE This command is used for removing one or more rows from a table (relation). Syntax − DELETE FROM table_name [WHERE condition]; For example − DELETE FROM MPTC WHERE Author="unknown"; SQL RDBMS Concepts 1. What is RDBMS? 2. What is table? 3. What is field? 4. What is column? 5. What is record or row? 6. What is NULL value? 7. SQL Constraints: The Following are commonly used constraints available in SQL: NOT NULL Constraint: Ensures that a column cannot have NULL value. DEFAULT Constraint: Provides a default value for a column when none is specified. UNIQUE Constraint: Ensures that all values in a column are different. PRIMARY Key: Uniquely identified each rows/records in a database table. FOREIGN Key: Uniquely identified a rows/records in any another database table. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. INDEX: Use to create and retrieve data from the database very quickly. SQL Basic Language elements The SQL language is sub-divided into several language elements, including: ❖Statements: which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics. ❖Queries: which retrieve data based on specific criteria. ❖Expressions: which can produce either scalar values or tables consisting of columns and rows of data. Clauses: which are in some cases optional, constituent components of statements and queries. ❖Whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Predicates: which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow. Introduction to SQL Statement/ commands Statement Descriptions Data Manipulation SELECT Retrieve data from the database INSERT Adds new rows of the data to the database DELETE Removes rows of the data from the database UPDATE Modifies existing database data Data Definition CREATE TABLE Adds a new table to the database DROP TABLE Removes a table from the database ALTER TABLE Change the structure of an existing table CREATE VIEW Adds a new view to the database DROP VIEW Removes a view from the database CREATE INDEX. Builds an index for a column DROP INDEX Remove an index for a column CREATE SYNOMY Define an alias for a table name DROP SYNOMY Remove an alias for a table name COMMENT Defines a remarks for a table or column LABEL Defines a little for a table or column Access Control GRANT Grant user access privileges REVOKE Removes user access privileges SQL key words ✓keywords are words that are reserved words that are not used as a user defined data. The most commonly used SQL key words ADA DEC GRANT NUMERIC ALL DECIMAL GROUP OF AND DECLARE HAVING ON ANY DEFUALT IN OPEN AS DELETE INDICATOR OPTION ASC DESC INSERT OPEN AUTHORIZATION DISTNICT INT OPTION AVG DOUBLE INTEGER OR BEGIN END INTO ORDER BETWEEN ESCAPE IS PRIMARY BY EXEC KEY REAL C EXISTS LANGAUGE SELECT CHAR FETCH LIKE SET CHARACTER FLOAT MAX SOME CHECK FOR MIN SUM CLOSE FOREIGN MODULE TABLE COBOL FORTRAN NOT TO UNION COMMIT FOUND NULL UPDATE CONTINUE FROM CURRENT USER COUNT GO CURSOR VIEW CREATE GOTO WITH WHERE WORK Data Types There is a standard that specifies various types of data that can be stored in a SQL-based database and manipulated by the SQL languages Character strings: Data type Description Storage Fixed-length character string. Maximum 8,000 characters char(n) or character(n) varchar(n) Variable-length character string. Maximum 8,000 characters varchar(max) Variable-length character string. Maximum 1,073,741,824 characters text Variable-length character string. Maximum 2GB of text data Unicode strings: Data type Description Stora ge nchar(n) Fixed-length Unicode data. Maximum 4,000 characters nvarchar(n) Variable-length Unicode data. Maximum 4,000 characters nvarchar(ma Variable-length Unicode data. Maximum x) 536,870,912 characters ntext Variable-length Unicode data. Maximum 2GB of text data Binary types: Data type Description Stora ge bit Allows 0, 1, or NULL binary(n) Fixed-length binary data. Maximum 8,000 bytes varbinary(n) Variable-length binary data. Maximum 8,000 bytes varbinary(m Variable-length binary data. Maximum 2GB ax) image Variable-length binary data. Maximum 2GB Data type Description Number types: Storage tinyint Allows whole numbers from 0 to 255 1 byte smallint Allows whole numbers between -32,768 and 32,767 2 bytes int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes bigint Allows whole numbers between -9,223,372,036,854,775,808 and 8 bytes 9,223,372,036,854,775,807 decimal(p,s) Fixed precision and scale numbers. 5-17 bytes Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 numeric(p,s) Fixed precision and scale numbers. 5-17 bytes Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308. 4 or 8 bytes The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53. real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes Date types: Data type Description Storage datetime From January 1, 1753 to December 31, 9999 with an accuracy 8 bytes of 3.33 milliseconds datetime2 From January 1, 0001 and December 31, 9999 with an accuracy 6-8 of 100 nanoseconds bytes smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 4 bytes minute date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes timestamp Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable Other data types: Data type Description sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp uniqueident Stores a globally unique identifier (GUID) ifier xml Stores XML formatted data. Maximum 2GB cursor Stores a reference to a cursor used for database operations table Stores a result-set for later processing Fixed-length character strings: columns holding these types of data typically store names of people and companies, addresses, descriptions, and so on. Integers: columns holding this types of data typically store counts, quantities, ages, and so on. Integer’s columns are also frequently used to contain Id numbers, such as customers, employee. And order numbers. Decimal numbers: columns with this type store numbers that have fractional parts and must be calculated exactly, such as rates and percentages. They are also frequently used to store money amounts. Floating point numbers: columns with this types are used to store scientific numbers which calculated and its values are estimated in powers of numbers. Example: weights, and longer distances Extended data Types ✓Variable-length Character string: SQL which supports VARCHAT data. Which allows a column to store character strings that vary in length from row to row, up to some maximum length. ✓Dates and times: supports for date/time values. ✓Boolean data: supports logical(TRUE or FALSE) values as an explicit type. Constants In some SQL statements a numeric, character, or date data value must be expressed in text form. Numeric constant Integers and decimal constants ( also called exact numeric literals) are written as ordinary decimal numbers in SQL statements, with an optional leading plus or minus sign Example: 200 +345.95 -500 789.00 String Constant ✓The ANSI/ISO standard specifies that SQL constants for character data be enclosed in single quotes (‘……’) Example: ‘G/egziyabher’ ‘Addis Ababa’ If a single quotes is to be used included in the constant text, it is written within the constant as two consecutive single quote characters. This is constant value: SQL Syntax Database Tables ✓A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data. SQL Statements ✓Most of the actions you need to perform on a database are done with SQL statements. ✓The following SQL statement selects all the records in the "Customers" table: SELECT Syntax SELECT column1, column2, …FROM table_name; SELECT name ,age FROM Customers; ->Retrieve name and age from relation SELECT * FROM Customers; ->Retrieve all records from relation Generally some of the most important sql commands SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index SQL CREATE DATABASE Statement The CREATE DATABASE statement is used to create a new SQL database. Syntax CREATE DATABASE databasename; Example CREATE DATABASE MPTC; NOTICE : Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the SQL command Show databases; SQL DROP DATABASE Statement The DROP DATABASE statement is used to drop an existing SQL database. Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database! Syntax DROP DATABASE databasename; BACKUP DATABASE for SQL Server The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database. Syntax BACKUP DATABASE databasenameTO DISK = 'filepath'; Example BACKUP DATABASE testDBTO DISK = 'D:\backups\testDB.bak'; BACKUP WITH DIFFERENTIAL Statement ✓A differential back up only backs up the parts of the database that have changed since the last full database backup. Syntax BACKUP DATABASE databasename TO DISK = 'filepath' WITH DIFFERENTIAL; Example BACKUP DATABASE testDBTO DISK 'D:\backups\testDB.bak‘ WITH DIFFERENTIAL; CREATE TABLE Statement ✓The CREATE TABLE statement is used to create a new table in a database. Syntax CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype,....); ✓The column parameters specify the names of the columns of the table. ✓The data type parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.). Example The following example creates a "Persons" table with five columns Called: PersonID, LastName, FirstName, Address, and City: CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); Create Table Using Another Table ❖A copy of an existing table can also be created using CREATE TABLE. ❖The new table gets the same column definitions. All columns or specific columns can be selected. ❖If you create a new table using an existing table, the new table will be filled with the existing values from the old table. Syntax CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE....; Example CREATE TABLE TestTable AS SELECT customername, contactname FROM customers; This SQL creates a new table called "TestTables" (which is a copy of the "Customers" table): DROP TABLE Statement The DROP TABLE statement is used to drop an existing table in a database. Syntax DROP TABLE table_name; Example DROP TABLE Shippers; The SQL statement drops the existing table "Shippers": TRUNCATE TABLE ✓The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself. Syntax TRUNCATE TABLE table_name ; ALTER TABLE Statement ✓The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ✓It is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column ✓To add a column in a table, use the following syntax: ALTER TABLE table_nameADD column_name datatype; Example ALTER TABLE Customers ADD Email varchar(255); ALTER TABLE - DROP COLUMN ✓To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column): syntax ALTER TABLE table_name DROP COLUMN column_name; Example ALTER TABLE Customers DROP COLUMN Email; ALTER TABLE - ALTER/MODIFY COLUM ✓To change the data type of a column in a table, use the following syntax ALTER TABLE table_name ALTER COLUMN In SQL Server / MS Access: column_name datatype; ALTER TABLE Persons ADD DateOfBirth date; ALTER TABLE table_nameMODIFY COLUMN column_name datatype; My SQL / Oracle (prior version 10G): SQL Constraints: ✓Constraints are the rules enforced on data columns on table. ✓ These are used to limit the type of data that can go into a table. ✓ This ensures the accuracy and reliability of the data in the database. ✓ Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table. ✓Following are commonly used constraints available in SQL: NOT NULL Constraint: Ensures that a column cannot have NULL value. DEFAULT Constraint: Provides a default value for a column when none is specified. UNIQUE Constraint: Ensures that all values in a column are different. PRIMARY Key: Uniquely identified each rows/records in a database table. FOREIGN Key: Uniquely identified a rows/records in any another database table. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. INDEX: Use to create and retrieve data from the database very quickly. 1. NOT NULL Constraint ❑By default, a column can hold NULL values. ❑If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. ❑A NULL is not the same as no data, rather, it represents unknown data Example CREATE TABLE Persons (ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int); ❑To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL: Example ALTER TABLE Persons MODIFY Age int NOT NULL; 2. PRIMARY KEY Constraint ❑A primary key is a field in a table which uniquely identifies each row/record in a database table. ❑Primary keys must contain unique values. ❑A primary key column cannot have NULL values. ❑A table can have only one primary key, which may consist of single or multiple fields. ❑When multiple fields are used as a primary key, they are called a composite key. ❑If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s). For example1 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID)); On MySQL / SQL Server / Oracle / MS Access: For example2 CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int); On SQL Server / Oracle / MS Access: ❑To create a PRIMARY KEY constraint on the "ID" column when CUSTOMERS table already exists, use the following SQL syntax: ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID); 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). defining a PRIMARY KEY constraint on multiple columns ❑For defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: Create table customers( id int not null, name varchar (20) not null, age int not null, address char (25) , salary decimal (18, 2), primary key (id, name) ); Delete Primary Key: You can clear the primary key constraints from the table, Use Syntax: ALTER TABLE CUSTOMERS DROP PRIMARY KEY ; 3. FOREIGN Key ❑A foreign key is a key used to link two tables together. ❑This is sometimes called a referencing key. ❑Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. ❑The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table On here we have do two different tables customer and ord create table customers( id int not null, name varchar (20) not null, age int not null, address char (25) , salary decimal (18, 2), primary key (id) ); Customers table Create table orders ( order id int not null primary key, ordernumber int not null, personid int foreign key references persons(personid)); Order table ✓if orders table has already been created, and the foreign key has not yet been set, use the syntax for specifying a foreign key by altering a table. alter table orders add foreign key (customer_id) references customers (id); DROP a FOREIGN KEY Constraint ✓To drop a FOREIGN KEY constraint, use the following SQL: ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder; 4. UNIQUE Constraint ✓The UNIQUE Constraint prevents two records from having identical values in a particular column. ✓In the CUSTOMERS table, for example, you might want to prevent two or more people from having identical age. ✓ Example: For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, AGE column is set to UNIQUE, so that you can not have two records with same age: create table customers( id int not null, name varchar (20) not null, age int not null unique, address char (25) , salary decimal (18, 2), primary key (id) ); ❑if customers table has already been created, then to add a unique constraint to age column, you would write a statement similar to the following: ALTER TABLE Persons ADD UNIQUE (ID); or alter table customers modify age int not null unique; ❑you can also use following syntax, which supports naming the constraint in multiple columns as well: alter table customers add constraint myuniqueconstraint unique(age, salary); drop a unique constraint: ❑ To drop a unique constraint, use the following sql: alter table customers drop constraint myuniqueconstraint; 4. CHECK Constraint: ❑The CHECK Constraint enables a condition to check the value being entered into a record. ❑ If the condition evaluates to false, the record violates the constraint and isn’t entered into the table CHECK on CREATE TABLE FORE EXAMPLE CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18)); for defining a CHECK constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')); ❑If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you would write a statement similar to the following: ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK (AGE >= 18 ); OR ALTER TABLE CUSTOMERS ADD CHECK (Age>=18); DROP a CHECK ✓To drop a CHECK constraint, use the following SQL: Constraint alter table customers drop constraint chk_personage; OR ALTER TABLE CUSTOMERS DROP CONSTRAINT myCheckConstraint; 4. DEFAULT Constraint ❑The DEFAULT constraint provides a default value to a column ❑The default value will be added to all new records IF no other value is specified. For example create table persons ( id int not null, lastname varchar(255) not null, firstname varchar(255), age int, city varchar(255) default 'sandnes'); The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE(): CREATE TABLE Orders (ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE()); To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL: ALTER TABLE PersonsADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City; DROP a DEFAULT Constraint ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT; The SQL SELECT Statement ❑The SELECT statement is used to select data from a database. ❑The data returned is stored in a result table, called the result-set. ❑SELECT Syntax SELECT column1, column2,...FROM table_name; ❑Here, column1, column2,... are the field names of the table you want to select data from. ❑If you want to select all the fields available in the table, use the following ❑ syntax: SELECT * FROM table_name; The SQL SELECT DISTINCT Statement ❑The SELECT DISTINCT statement is used to return only distinct (different) values. ❑Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT Syntax ❑SELECT DISTINCT column1, column2,...FROM table_name; SELECT Example Without DISTINCT The following SQL statement selects ALL (including the duplicates) values from the "Country" column in the "Customers" table: Example SELECT Country FROM Customers; 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 SQL WHERE Clause ✓The WHERE clause is used to filter records. ✓The WHERE clause is used to extract only those records that fulfill a specified condition. WHERE Syntax SELECT column1, column2,... FROM table_name WHERE condition; Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.! Example The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table: SELECT * FROM CustomersWHERE Country='Mexico'; Operators in The WHERE Clause The following operators can be used in the WHERE clause: Exercise: Select all records where the City column has the value "Berlin". SELECT * FROM Customers ____ ________ =_______ ; The SQL AND, OR and NOT Operators ✓The WHERE clause can be combined with AND, OR, and NOT operators. ✓The AND and OR operators are used to filter records based on more than one condition: ✓The AND operator displays a record if all the conditions separated by AND are TRUE. ✓The OR operator displays a record if any of the conditions separated by OR is TRUE. ✓The NOT operator displays a record if the condition(s) is NOT TRUE. We can use the table below for the syntax OR Syntax SELECT column1, column2,...FROM table_name WHERE condition1 OR condition2 OR condition3...; Example SELECT * FROM Customers WHERE City='Berlin' OR City='München'; NOT Syntax SELECT column1, column2,...FROM table_name WHERE NOT condition; Example SELECT * FROM CustomersWHERE Country='Germany' OR Country='Spain'; AND Syntax SELECT column1, column2,...FROM table_name WHERE condition1 AND condition2 AND condition3...; Example SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; Combining AND, OR and NOT ✓You can also combine the AND, OR and NOT operators. ✓The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München“ ✓SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' The following SQL statement OR selects all fields from City='München'); "Customers" where country is NOT "Germany" and NOT "USA": Example SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA'; Exercise: Select all records where the City column has the value Except 'Berlin' and the PostalCode column has the value 12209. _____* FROM Customers ____City = 'Berlin' ____ _____ = 12209; The SQL ORDER BY Keyword ✓The ORDER BY keyword is used to sort the result-set in ascending or descending order. ✓The ORDER BY keyword sorts the records in ascending order by default. ✓To sort the records in descending order, use the DESC keyword. ORDER BY Syntax ✓SELECT column1, column2,...FROM table_name ORDER BY column1, column2,... ASC|DESC; Example1 SELECT * FROM Customers ORDER BY Country; This SQL statement selects all customers from the "Customers" table, sorted by the "Country" column: Example2 SELECT * FROM Customer sORDER BY Country DESC; SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column: ORDER BY Several Columns Example 3 SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column: Exercise: Select all records from the Customers table, sort the result alphabetically by the column City. SELECT * FROM Customers __________ ___________; SQL INSERT INTO Statement ✓The INSERT INTO statement is used to insert new records in a table. ✓INSERT INTO Syntax ✓It is possible to write the INSERT INTO statement in two ways. I. The first way specifies both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...); Example INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'); ii. do not need to specify the column names in the SQL query. The INSERT INTO syntax would be as follows: INSERT INTO table_nameVALUES (value1, value2, value3,...); Example INSERT INTO Customers VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'); Exercise: Insert a new record in the Customers table. _______Customers ___CustomerName, Address, City, PostalCode,Country___ ___ 'Hekkan Burger','Gateveien 15','Sandnes','4306','Norway‘___; SQL NULL Values ✓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. ✓It is A field with a NULL value is a field with no value. NULL Syntax SELECT column_names FROM table_nameWHERE column_name IS NULL; Example SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL; IS NOT NULL Syntax SELECT column_namesFROM table_nameWHERE column_name IS NOT NULL; Example SELECT CustomerName, ContactName, AddressFROM CustomersWHERE Address IS NOT NULL; Exercise: Select all records from the Customers where the PostalCode column is empty. SELECT * FROM Customers WHERE ___ ___ __ ; SQL UPDATE Statement The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax UPDATE table_nameSET column1 = value1, column2 = value2,...WHERE condition; Notice The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated! 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 will be updated. ✓The following SQL statement will update the contact name to "Juan" for all records where country is "Mexico": Example UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico'; Notice The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated! Example UPDATE CustomersSET ContactName='Juan'; ALL records will be updated here SQL DELETE Statement ✓The DELETE statement is used to delete existing records in a table. DELETE Syntax DELETE FROM table_name WHERE condition; ✓The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted! Example DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste'; Delete All Records It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name; The following SQL statement deletes all rows in the "Customers" table, without deleting the table: Example DELETE FROM Customers; TOP, LIMIT or ROWNUM Clause The SQL SELECT TOP Clause ✓The SELECT TOP clause is used to specify the number of records to return. ✓The SELECT TOP clause is useful on large tables with thousands of records. ✓Returning a large number of records can impact performance. Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM. SELECT TOP number FROM table_name WHERE Syntax condition; Example SELECT TOP 3 * FROM Customers; Example1 SELECT TOP 3 * FROM Customers WHERE Country='Germany'; MySQL Syntax for LIMIT : SELECT column_name(s)FROM table_nameWHERE condition LIMIT number; Example2 SELECT * FROM Customers WHERE Country='Germany'LIMIT 3; Example3 SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM

Related

Transcript

Tags

Upgrade to continue