Presentation SQL

Learning objective

To make reports using extracted data

Lesson objectives:
explain the purpose of data dictionary
compare the data definition language (DDL), and the data manipulation language (DML)
describe the basic SQL queries for working with tables in a database: CREATE, ALTER and DROP
describe the basic SQL queries for working with one table in a database: SELECT, UPDATE, INSERT and DELETE
use SQL SELECT for data selection in more tables

To change the values in one or more columns of the table used UPDATE statement.

UPDATE table_name SET Field = new_Value
WHERE selection condition;

Example:
UPDATE Customers SET ContactName=‘Student’, City=‘Taraz’ WHERE CustomerID=2;

After UPDATE statements, records fields CustomerName, City in Customers table has changed

Activity

Go to this link
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

Update London to Berlin for CustomerID = 4
Perform the task. Show your answers for teacher

To add records to the table, use the INSERT statement

INSERT INTO table_name (field1, field2, field3, ...)
VALUES (value1, value2, value3, ...);

Data Manipulation Language, DML:

Example:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (‘Student', ‘Anuar Samatov', ‘Satpayev 2', ‘Taraz', ‘000000', 'Kazakhstan');

After the INSERT INTO proposals at the end of the table create a new record with the given values.

Activity

Go to this link
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

INSERT VALUES ('Bala', 'Askar Nagay', 'Abai 1', 'Taraz', '200000', 'Kazakhstan');

Data Manipulation Language, DML:

To delete rows from a table, use a DELETE statement


DELETE FROM table-name
WHERE selection condition

Example:
DELETE FROM Customers WHERE CustomerID=3;

After the proposal DELETE FROM, the third record with values completely delete.

Activity

Go to this link
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

Delete row where CustomerID=12
Perform the task. Show your answers for teacher

Practical work

Table: actor_info

INSERT Query

INSERT INTO actor_info VALUES
(1, ’Leonardo’ , ’DiCaprio’ ,35),
(2, ’Matt’ , ’Damon’ ,61),
(3, ’Jack’ , ’Nicholson’ ,75),
(4, ’Mark’ , ’Wahlberg’ ,37),

SELECT Query

SELECT * FROM actor_info;

SELECT Query

SELECT actor_id, total_films
FROM actor_info;

UPDATE Query

UPDATE actor_info
SET total_films = 36
WHERE actor_id = 1;

DELETE Query

DELETE FROM actor_info
WHERE total_films > 70

DELETE Query

DELETE FROM actor_info

Did you learn useful information for yourself?
Where did you have difficulties?
What would like to explore in the next lesson?

Used links:

sqlzoo.net
https://en.wikibooks.org/wiki/A-level_Computing_2009/AQA/Problem_Solving,_Programming,_Operating_Systems,_Databases_and_Networking/Databases/SQL
http://articles.org.ru/cn/showdetail.php?cid=7163
http://www.w3schools.com/sql/default.asp
http://www.site-do.ru/db/sql9.php
https://ru.wikipedia.org/wiki/
AQA A2 p. 161-163