Tuesday, May 31, 2016

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 <tablesWHERE <condition>

Retrieve data satisfying multiple conditions 
>SELECT <columnFROM  <tablesWHERE <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:

Example




SELECT 
    productCode, productName, textDescription
FROM
    products T1
        INNER JOIN
    productlines T2 ON T1.productline = T2.productline;


  • 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 ],
  ...
);

No comments:

Post a Comment