Introduction To Structured Query Language SQL SQL SQL

Introduction To Structured Query Language (SQL)

SQL • SQL is a standard language for accessing databases. • Our SQL tutorial will teach you how to use SQL to access and manipulate data in: MS Access SQL Server database systems.

What is SQL? • SQL stands for Structured Query Language • SQL lets you access and manipulate databases • SQL is an ANSI (American National Standards Institute) standard

What Can SQL do? • • • SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

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

Student

SELECT Statements • In this tutorial we will use the well-known sample database (included in MS Access and MS SQL Server). – The following SQL statement selects all the records in the table: – Statement: – SELECT * FROM Table; – E. g. : SELECT * FROM Student;

SELECT Statements • SELECT column_name, column_name FROM table_name; • Select id, Firstname, age From Student;

SQL SELECT DISTINCT Statement • The SELECT DISTINCT statement is used to return only distinct (different) values. – SELECT DISTINCT column_name, column_name FROM table_name;

SQL WHERE Clause • The WHERE clause is used to filter records. – SELECT * FROM Student WHERE Field_Name ="Text Value"; – SELECT * FROM Student WHERE Field_Name =Numeric Value;

Operators in The WHERE Clause Operator Description = Equal <> Not equal. Note: In some versions of SQL this operator may be written as != > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN To specify multiple possible values for a column

AND & OR Operators • The AND & OR operators are used to filter records based on more than one condition. • SELECT * FROM Student WHERE City =Nizwa" AND Age < 30; • SELECT * FROM Student WHERE City =Nizwa" OR Age < 30; • SELECT * FROM Student WHERE City=Nizwa" AND (lastname= "said" OR firstname="ali");

Operators in The WHERE Clause • The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. • SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value 1 AND value 2 ; • Not Between used to NOT Include SELECT Firstname, Lastname, city, age FROM student WHERE city NOT BETWEEN "Muscat" AND "Izki";

IN Operator • The IN operator allows you to specify multiple values in a WHERE clause. • SELECT column_name(s) FROM table_name WHERE column_name IN (value 1, value 2, . . . );

LIKE Operator • The LIKE operator is used to search for a specified pattern in a column. • SELECT * FROM Student WHERE City LIKE "s*";

ORDER BY Keyword • The ORDER BY keyword is used to sort the resultset. • SELECT * FROM student • ORDER BY city; • SELECT * FROM student ORDER BY city ASC /DESC; • SELECT * FROM student ORDER BY city, firstname; • SELECT * FROM student ORDER BY city ASC, lastname DESC;

INSERT INTO Statement • The INSERT INTO statement is used to insert new records in a table. • INSERT INTO Student(First. Name, Last. Name, City, age, Mob) VALUES (‘Nour', Elshaiekh', Halfa', 42, 5654566);

UPDATE Statement • The UPDATE statement is used to update records in a table. • UPDATE Student SET Last. Name= “Mussab”, City= “Suhar” WHERE First. Name= “Ali”;

Delete Statement • The DELETE statement is used to delete records in a table. • DELETE FROM Student WHERE First. Name="Ali" AND Last. Name="Said" ;

SELECT TOP Clause • The SELECT TOP clause is used to specify the number of records to return. • SELECT TOP 2 * FROM Student;

SELECT TOP Clause • SQL statement selects the first ? % of the records from the table: • SELECT TOP 20 PERCENT * FROM Student;

SQL JOIN An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. SELECT abs. name, Student. First. Name, ab. age FROM student INNER JOIN ab ON ab. Name=student. First. Name;

SQL Aliases • SQL aliases are used to give a database table, or a column in a table, a temporary name. • Basically aliases are created to make column names more readable. • SELECT First. Name AS CEMIS FROM Student;

The SQL UNION Operator • The UNION operator is used to combine the result-set of two or more SELECT statements SELECT First. Name FROM Student UNION SELECT First. Name FROM ab; SELECT City FROM Student UNION SELECT First. Name From ab ORDER BY City;