7Computer Science_10_Revision+Information Systems

SQL

Lesson objectives define data types in database (SQL); create a single-table database (SQL); create multi-table database (SQL); create a form for data entry (SQL); create…

Lesson objectives	define data types in database (SQL); create a single-table database (SQL); create multi-table database (SQL); create a form for data entry (SQL); create queries using extracted data (SQL); generate reports using extracted data (SQL);
Success criteria	Be able to use SELECT Be able to create queries in SQL

A query is a means of selecting the necessary information from the database

A query is a means of selecting the necessary information from the database. The question, formed with respect to the database, is the query.

SQL queries are queries that are compiled (programmers) from a sequence of

SQL queries are queries that are compiled (programmers) from a sequence of SQL statements. These instructions specify what to do with the input data set to generate the output set. All Access requests are built on the basis of SQL queries, in order to view them, you must execute the View / SQL command in the active query design window.

SQL SQL (structured query language) is a formal non-procedural language used to create, modify, and manage data in an arbitrary relational database

SQL

SQL (structured query language) is a formal non-procedural language used to create, modify, and manage data in an arbitrary relational database

Initially, SQL was the main way the user worked with the database and allowed to perform the following set of operations: creating a new table…

Initially, SQL was the main way the user worked with the database and allowed to perform the following set of operations:

creating a new table in the database;
adding new records to the table;
change records;
deletion of records;
selection of records from one or several tables (according to the specified condition);
change tables structures.

The language of data manipulation

The language of data manipulation DML

DML - Data Manipulation Language. The language of data manipulation. Used to work with information stored in the database.
The main teams of this group are:
Select - readout of information.
Insert - adding information.
Update - information update.
Delete - delete information.

Data collection SELECT * FROM Sumproduct

Data collection

SELECT * FROM Sumproduct
What will be the result?
SELECT Product FROM Sumproduct
SELECT Product, Quantity FROM Sumproduct

Sorting data SELECT * FROM Sumproduct

Sorting data

SELECT * FROM Sumproduct ORDER BY Amount
Although by default the ORDER BY clause sorts in ascending order, we can also register the sorting of values in descending order. To do this, at the end of each field, put the DESC statement (which is an abbreviation for DESCENDING).
SELECT * FROM Sumproduct ORDER BY Amount DESC

Filtering data SELECT * FROM Sumproduct

Filtering data

SELECT * FROM Sumproduct WHERE Product = 'Bikes
What will be the result?
SELECT * FROM Sumproduct WHERE Amount > 40000 ORDER BY Amount

The table below lists the conditional statements supported by

The table below lists the conditional statements supported by SQL:

Filter by value range (BETWEEN)

Filter by value range(BETWEEN).

SELECT * FROM Sumproduct WHERE Amount BETWEEN 1000 AND 2000

Selection of empty records (IS

Selection of empty records (IS NULL).
SELECT * FROM Sumproduct WHERE Amount IS NULL

Advanced filtering (AND, OR) SELECT *

Advanced filtering (AND, OR)

SELECT * FROM Sumproduct WHERE Amount > 40000 AND City = 'Toronto'

What will be the result?

SELECT * FROM Sumproduct WHERE Month= 'April' OR Month= 'March'

Advanced filtering (operator IN)

Advanced filtering (operator IN)

SELECT * FROM Sumproduct WHERE ID IN (4, 12, 58, 67)

What will be the result?
SELECT * FROM Sumproduct WHERE NOT City IN ('Toronto', 'Montreal')

Metacharacter () SELECT FROM

Metacharacter (*)

SELECT * FROM Sumproduct WHERE Product LIKE '*Skis*'

SQL functions for processing numbers

SQL functions for processing numbers

SELECT Amount, SQR(Amount) AS Amount_SQR

SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct

Statistical functions of SQL

Statistical functions of SQL

SELECT SUM(Quantity) AS Sum1 FROM

SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = 'April'
SELECT AVG(Amount) AS Avg1 FROM Sumproduct
What will be the result?

Скачать файл