WDP Database Queries - Exercise

Identify the purpose and syntax and provide an example code and output for each of the query.

• SELECT

Purpose: Retrieve or extract data from a database.

SQL SELECT Syntax

SELECT column_name,column_name
FROM table_name;

and

SELECT * FROM table_name;

Retrieve from all columns 

>SELECT * FROM <tables>

>SELECT * FROM teachers;

Retrieve from selected columns 

>SELECT <column 1>,  <column 2>  FROM <table>

Retrieve unique values 

>SELECT DISTINCT <columnName>  FROM <table>

Retrieve data satisfying a given condition 

>SELECT <column>  FROM <tables> WHERE <condition>

Retrieve data satisfying multiple conditions 

>SELECT <column> FROM  <tables> WHERE <condition> AND <condition>

Other SELECT purposes

• JOIN

Purpose: used to combine rows from two or more tables, based on a common field between them.

Different SQL JOINs

Before we continue with examples, we will list the types of the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

The syntax of the MySQL INNER JOIN clause is as follows:

1 2 3 4 5 6

SELECT column_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 ... WHERE where_conditions;

Example

SELECT 

    productCode, productName, textDescription

FROM

    products T1

        INNER JOIN

    productlines T2 ON T1.productline = T2.productline;

Output

• INSERT INTO

Purpose: The INSERT INTO statement is used to insert new records in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

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

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

 INSERT INTO teachers (id, name, age) VALUES (NULL, ‘John’, ‘12’);

• UPDATE

Purpose: UPDATE statement is used to update records in a table.

SQL UPDATE Syntax

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

OR

The syntax for the SQL UPDATE statement when updating a table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

• DELETE

Purpose: The DELETE statement is used to delete records in a table.

Syntax

The syntax for the DELETE statement in SQL is:

DELETE FROM table
[WHERE conditions];

• DROP

Purpose: The SQL DROP TABLE statement allows you to remove or delete a table from the SQL database.

DROP INDEX Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

OR

DROP TABLE table_name;

• CREATE TABLE

Purpose: The SQL CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the CREATE TABLE statement in SQL is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);