Презентация SQL (1)

10.3.3.1 создавать отчеты, используя извлеченные данные (SQL (эс кю эль))

Цели урока:
Объяснять назначение словаря данных
Сравнивать язык определения данных DDL и язык манипулирования данными DML
Описывать основные команды SQL SELECT, UPDATE, INSERT and DELETE
Использовать SQL SELECT для выбора данных из нескольких таблиц

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

Практика

Пройдите по ссылке
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

Обновите London to Berlin для CustomerID = 4
После выполнения задания, покажите ответ учителю

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.

Практика

Пройдите по ссылке
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.

Практика

Пройдите по ссылке
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

Удалите строку где CustomerID=12
После выполнения задания, покажите ответ учителю

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