SQL SELECT Statement Syntax The SQL SELECT statement

Syntax • The SQL SELECT statement is used to SELECT data FROM a table.

How does it work? DB 1 Response Request Server Select * FROM student My.

Example • To SELECT the content of columns named “name" and “class" FROM the

Selecting Particular Columns • To SELECT all columns FROM the "Persons" table, use a

Semicolon after SQL Statements? • Semicolon is the standard way to separate each SQL

Exercise 1. Write the SQL query to get name, class, email of table student.

ALIAS • With SQL, aliases can be used for column names and table names.

Example • Here table student: • SELECT name AS ‘Student Name’ FROM student Copyright

Exercise 1. Write the SQL query to list all the students and change to

DISTINCT Keyword The DISTINCT keyword is used to return only distinct (different) values. •

Example • To SELECT ALL values FROM the column named “class" we use a

Exercise 1. List all the job of the employee (not duplicate) Copyright © 2007

Any Question? Copyright © 2007 - CIST 14

SQL WHERE Clause The WHERE clause is used to specify a selection criterion. •

Example • To SELECT only the student that is ‘Male’, we add a WHERE

Using the Quotes • • Note that we have used single quotes around the

Exercise 1. Write the SQL query to get all the female student FROM student

The LIKE Condition The LIKE condition is used to specify a search for a

Example • The following SQL statement will return students with name that start with

Example • The following SQL statement will return students with name that end with

Example • The following SQL statement will return students with name that contain the

Exercise 1. Write the SQL query to get all the students that their name

Any Question? Copyright © 2007 - CIST 26

Sorting Search Results with ORDER BY • The ORDER BY clause is used to

Sort More Fields • This SQL Statement – SELECT * FROM student ORDER BY

ASC & DESC • This SQL Statement – SELECT * FROM student ORDER BY

Exercises 1. Write the SQL query to list all the students by sorting their

Any Question? Copyright © 2007 - CIST 32

Limiting Search Results with LIMIT • The LIMIT clause is used to limit the

Exercise 1. List the 2 first employees order by name FROM A to Z

Any Question? Copyright © 2007 - CIST 37

AND & OR • • • AND and OR join two or more conditions

Example (AND) • Here table student: SELECT * FROM student WHERE gender=“Male” AND pob=“Phnom

Example (OR) • Here table student: SELECT * FROM student WHERE gender=“Male” OR pob=“Phnom

Example (AND & OR) • • You can also combine AND and OR (use

Exercise 1. Write the SQL query to list the students that is male and

Any Question? Copyright © 2007 - CIST 44

IN & NOT IN The IN operator may be used if you know the

Example (IN) • Here table student: SELECT * FROM student WHERE name IN (‘BON

Example (NOT IN) • Here table student: SELECT * FROM student WHERE name NOT

Exercise 1. Use IN & NOT IN Statement to list students that learn in

Any Question? Copyright © 2007 - CIST 50

BETWEEN. . . AND The BETWEEN. . . AND operator SELECTs a range of

Example 1 • Here table student: • SELECT * FROM student WHERE YEAR(dob) BETWEEN

Example 2 • Here table student: • SELECT * FROM student WHERE YEAR(dob) NOT

Quiz 1. 2. 3. Which of the following queries SELECTs all data stored in

Exercise 1. Write a query that lists all information about employees who work for

Any Question? Copyright © 2007 - CIST 57

Slides: 58

Download presentation

SQL SELECT Statement

Syntax • The SQL SELECT statement is used to SELECT data FROM a table. The tabular result is stored in a result table (called the result-set). Syntax: SELECT columns FROM tables [WHERE conditions] [GROUP BY group [HAVING group_conditions]] [ORDER BY sort_columns] [LIMIT limits]; Note: SQL statements are not case sensitive. SELECT is the same as SELECT. Copyright © 2007 - CIST 2

Example • To SELECT the content of columns named “name" and “class" FROM the database table called “student", use a SQL SELECT statement like this: – SELECT name, class FROM student Copyright © 2007 - CIST 4

Semicolon after SQL Statements? • 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. Copyright © 2007 - CIST 6

Exercise 1. Write the SQL query to list all the students and change to name of the field below: • “name” to “Student Name” • “dob” to “Date of Birth” • “pob” to “Place of Birth” Copyright © 2007 - CIST 10

DISTINCT Keyword The DISTINCT keyword is used to return only distinct (different) values. • The SQL SELECT statement returns information FROM table columns. But what if we only want to SELECT distinct elements? • With SQL, all we need to do is to add a DISTINCT keyword to the SELECT statement: Syntax: • – SELECT DISTINCT column_name(s) FROM table_name Copyright © 2007 - CIST 11

Example • To SELECT ALL values FROM the column named “class" we use a SQL SELECT statement like this: – SELECT class FROM student • Note that “ 12 A”, “ 12 B”, “ 12 C" is listed twice in the result-set. • To SELECT only DIFFERENT values FROM the column named "Company" we use a SELECT DISTINCT statement like this: – SELECT DISTINCT class FROM student Copyright © 2007 - CIST 12

SQL WHERE Clause

SQL WHERE Clause The WHERE clause is used to specify a selection criterion. • To conditionally SELECT data FROM a table, a WHERE clause can be added to the SELECT statement. Syntax SELECT column FROM table WHERE column OPERATOR value • Copyright © 2007 - CIST 16

Using the Quotes • • Note that we have used single quotes around the conditional values in the examples. SQL uses single quotes around text values (most database systems will also accept double quotes). Numeric values should not be enclosed in quotes. For text values: – This is correct SELECT * FROM student WHERE name=‘Sok‘ – This is wrong SELECT * FROM student WHERE name=Sok For numeric values: – This is correct SELECT * FROM student WHERE YEAR(dob)>1965 – This is wrong SELECT * FROM student WHERE YEAR(dob)>'1965' Copyright © 2007 - CIST 19

Exercise 1. Write the SQL query to get all the female student FROM student database. 2. Write the SQL query to get all the student that learn in class 12 A. 3. Write the SQL query to get all the student that was born in 1985. Copyright © 2007 - CIST 20

The LIKE Condition The LIKE condition is used to specify a search for a pattern in a column. Syntax • SELECT column FROM table WHERE column LIKE pattern Note: A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern. Copyright © 2007 - CIST 21

Exercise 1. Write the SQL query to get all the students that their name start with letter K. 2. Write the SQL query to get all the students that their name end with letter L. 3. Write the SQL query to get all the students that their name contains letter A. Copyright © 2007 - CIST 25

SQL ORDER BY Statement

Exercises 1. Write the SQL query to list all the students by sorting their name FROM A to Z. 2. Write the SQL query to list all the students by sorting their date of birth ascending. Copyright © 2007 - CIST 31

SQL LIMIT Statement

Limiting Search Results with LIMIT • The LIMIT clause is used to limit the number and range of rows that are returned FROM a query. For example, consider the following query: SELECT * FROM student limit 5 Copyright © 2007 - CIST 34

SQL AND & OR Statement

AND & OR • • • AND and OR join two or more conditions in a WHERE clause. The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true. Truth Table Condition II AND OR 0 0 0 1 1 1 Copyright © 2007 - CIST 39

Example (AND & OR) • • You can also combine AND and OR (use parentheses to form complex expressions) Here table student: SELECT * FROM student WHERE gender=“Male” AND (class=“ 12 A” OR class=“ 12 B”) Copyright © 2007 - CIST 42

SQL IN & NOT IN Statement

IN & NOT IN The IN operator may be used if you know the exact value you want to return for at least one of the columns. Syntax: • – SELECT column_name FROM table_name WHERE column_name IN (value 1, value 2, . . ) The opposite of IN Statement is NOT IN. Syntax: • – SELECT column_name FROM table_name WHERE column_name NOT IN (value 1, value 2, . . ) Copyright © 2007 - CIST 46

Exercise 1. Use IN & NOT IN Statement to list students that learn in class A, B, D. 2. Try to query again without IN & NOT IN. 3. Use IN & NOT IN Statement to list students that not born in 1985 and 1987. Copyright © 2007 - CIST 49

SQL BETWEEN & NOT BETWEEN Statement

BETWEEN. . . AND The BETWEEN. . . AND operator SELECTs a range of data between two values. These values can be numbers, text, or dates. Syntax: • – SELECT column_name FROM table_name WHERE column_name BETWEEN value 1 AND value 2 The opposite of BETWEEN Statement is NOT BETWEEN. Syntax: • – SELECT column_name FROM table_name WHERE column_name NOT BETWEEN value 1 AND value 2 Copyright © 2007 - CIST 52

Quiz 1. 2. 3. Which of the following queries SELECTs all data stored in the client table? 1. SELECT * FROM client WHERE client. ID=2; 2. SELECT client. ID, name, address, contact. Person, contact. Number FROM client; 3. SELECT * FROM client limit 1; 4. SELECT all FROM client; Which of the following queries SELECTs all the programmers FROM the employee table? 1. SELECT * FROM employee WHERE job='Programmer'; 2. SELECT * FROM employee HAVING job='Programmer'; 3. SELECT * FROM employee WHERE job='Programmer' GROUP BY job HAVING job='Programmer'; 4. SELECT job FROM employee; Which of the following queries will not return the total number of employees in the employee table? 1. SELECT count(employee. ID) FROM employee; 2. SELECT count(employee. ID) AS total FROM employee; 3. SELECT count(distinct employee. ID) FROM employee; 4. SELECT count(employee. ID) FROM employee GROUP BY employee. ID; Copyright © 2007 - CIST 55

Exercise 1. Write a query that lists all information about employees who work for department 128. 2. Write a query that lists all the employee. IDs of employees who have worked for client number 1. 3. Write a query that returns the number of employees who know each skill listed in the employee. Skills table. Copyright © 2007 - CIST 56