SQL

Learning objective

• describe the basic SQL queries for working with one table in a database: SELECT, UPDATE, INSERT and DELETE

​

Assessment criteria

• uses SELECT statement to retrieve data
• uses INSERT statement to add data to table
• uses WHERE clause in queries
• uses AND, OR and NOT Operators in queries
• uses LIKE operator in queries

DML

It stands for Data Manipulation Language.It is used to add, retrieve or update the data.

It basically defines the column (Attributes) of the table. It add or update the row of the table. These rows are called as tuple.

Examples of DML:

• SELECT
• INSERT
• UPDATE
• DELETE

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...

FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

​

Try it yourself

​

Activity

1. Output all customer name and Contact name from customers table.
2. Output all country that stored customers table(does not allow duplication in results).
3. Output all customers name from Madrid.
4. Output all information of customers that CustomerId more than 10.

Descriptors:

• Uses SELECT statement
• Uses DISTINCT in sql queries
• Uses WHERE clause in sql queries
• Uses comparison operators in sql queries

INSERT INTO

The INSERT INTO statement is used to insert new records in a table.It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

​

​

​

​

​

​

Try it yourself

Activity

1. Add at least 5 customers from different city of Kazakhstan.

​

Descriptor:

• Uses INSERT INTO statement

Activity +

1. Output all customers from Kazakhstan.
2. Output all clients from Kazakhstan whose name contains the letter "a".
3. Output all customer whose CustomerID are even.
4. Output all customer whose CustomerID are even and from Kazakhstan.

Descriptors:

• Uses SELECT statement
• Uses AND operators in sql queries
• Uses LIKE operators in sql queries
• Uses WHERE clause in sql queries
• Uses comparison operators in sql queries

​

​

Try it yourself

​