SQL Basics (3)

1. SQL Full JOIN

(1) SQL full join keyword

• The full JOIN keyword returns a row whenever there is a match in one of the tables.

(2) syntax

• SELECT column_name (s) from Table_name1full JOIN table_name2 on Table_name1.column_name=table_name2.column_name

• Note: In some databases, full join is called full OUTER join.

(3) Example

• SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsfull JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname
  
  

2.SQL Union and SQL UNION ALL

(1) SQL Union operator

• The UNION operator is used to combine the result set of two or more SELECT statements.

• Note that the SELECT statement inside the UNION must have the same number of columns . The column must also have a similar data type . Also, the order of the columns in each SELECT statement must be the same.

(2) SQL Union syntax

• SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2

• Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.

(3) SQL UNION all syntax

• SELECT column_name (s) from Table_name1union Allselect column_name (s) from table_name2

• In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.

(4) Using the SQL Union command

• SELECT E_name from Employees_china
  UNION
  SELECT E_name from Employees_usa

(5) UNION all

• The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values.

• SQL Statement 1UNION allsql Statement 2

(6) Use the Union ALL command

• SELECT E_name from Employees_china
  UNION ALL
  SELECT E_name from Employees_usa

3.SQL SELECT into

The SQL SELECT into statement can be used to create a backup copy of the table.

(1) SELECT into statement

• The SELECT INTO statement selects data from one table and then inserts the data into another table.

• The SELECT into statement is commonly used to create a backup copy of a table or to archive records.

(2) SELECT into syntax

• You can insert all the columns into the new table:

SELECT *into new_table_name [in Externaldatabase] from Old_tablename

• Or just insert the desired column into the new table:

SELECT column_name (s) to new_table_name [in Externaldatabase] from Old_tablename

(3) SELECT into instance-make backup copy

• The following example makes a backup copy of the "Persons" table:

SELECT*
INTOPersons_backupfrom Persons

• The IN clause can be used to copy a table to another database:

SELECT*
INTOIN' Backup.mdb ' from Persons

• If we want to copy some of the fields, we can list them after the SELECT statement:

SELECTLastname,firstname
INTOPersons_backupfrom Persons

(4) SELECT into instance-with a WHERE clause

• We can also add a WHERE clause.

• The following example creates a table named "Persons_backup" with two columns by extracting information from the "Persons" table of people residing in "Beijing":

SELECTLastname,firstname
INTOPersons_backupfrom Persons
WHEREcity= ' Beijing '

(5) SELECT into instance-connected table

• It is also possible to select data from more than one table.

• The following example creates a new table named "Persons_order_backup" that contains the information obtained from the Persons and Orders two tables:

SELECTPersons.lastname,orders.orderno
INTOPersons_order_backup
FROMPersons
INNER JOINOrders
ONPersons.id_p=orders.id_p

4.SQL creat DATABASE

(1) Creat DATABASE statement

• Create database is used for creating databases.

(2) SQL creat DATABASE syntax

• CREATE DATABASE database_name

(3) SQL creat DATABASE Instance

• CREATE DATABASE my_db

• You can add database tables by using CREATE table .

5.SQL creat TABLE

(1) Creat TABLE statement

• The Creat table statement is used to create a table in the database.

(2) creat TABLE syntax

• CREATE table table name (column name 1 data type, column name 2 data type, column name 3 data type,....)

• The data type (DATA_TYPE) specifies what data type the column can hold. The following table contains the most commonly used data types in sql:

Data Type	Description
Integer (size) int (size) smallint (size) tinyint (size)	Holds integers only. Specify the maximum number of digits within the parentheses.
Decimal (SIZE,D) Numeric (SIZE,D)	Accommodates numbers with decimals. "Size" Specifies the maximum number of digits. "D" Specifies the maximum number of digits to the right of the decimal point.
char (size)	Holds a fixed-length string (which can hold letters, numbers, and special characters). Specifies the length of the string in parentheses.
varchar (size)	Accommodates variable-length strings that can hold letters, numbers, and special characters. Specifies the maximum length of the string in parentheses.
Date (YYYYMMDD)	accommodate the date.

(3) Creat TABLE instance

• This example shows how to create a table named "Person."

• The table contains 5 columns with column names: "Id_p", "LastName", "FirstName", "Address", and "City":

CREATE TABLE Persons (id_p int,lastname varchar (255), FirstName varchar (255), Address varchar (255), City varchar (255))

• The data type of the id_p column is int, which contains integers. The data type of the remaining 4 columns is varchar, with a maximum length of 255 characters.

• An empty "Persons" table looks like this:

City

id_p	LastName	FirstName	Address

• You can use the INSERT into statement to write data to an empty table.

6.SQL CONSTRAINTS

(1) SQL constraints

• Constraints are used to restrict the type of data that is joined to a table.

• You can specify constraints (through the CREATE TABLE statement) When you create the table, or you can (via the ALTER table statement) after the table is created.

• We will focus on the following constraints:

1. Not NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

5. CHECK

6. DEFAULT

In the following sections, we will explain each constraint in detail.

7.SQL not NULL constraint

• The NOT NULL constraint enforces that the column does not accept null values.

• A NOT NULL constraint forces a field to always contain a value . This means that if you do not add a value to the field, you cannot insert a new record or update the record.

• The following SQL statement enforces that the "id_p" column and the "LastName" column do not accept NULL values:

NOT NULLNOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255))

8.SQL UNIQUE Constraint

(1) SQL UNIQUE constraint

• Unique constraints uniquely identify each record in a database table.

• Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection.

• PRIMARY KEY has a UNIQUE constraint that is automatically defined .

• Note that each table can have multiple UNIQUE constraints , but there can be only one PRIMARY KEY constraint per table.

(2) SQL UNIQUE CONSTRAINT on creat TABLE

The following SQL creates a UNIQUE constraint in the "id_p" column when the "Persons" table is created:

• Mysql:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255),
UNIQUE (Id_P))

• Mysql/sol Server/oracle/ms Access:

UNIQUE, LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

• If you need to name a unique constraint and define a unique constraint for multiple columns, use the following SQL syntax:

Mysql/sol Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) )

(3) SQL UNIQUE CONSTRAINT on ALTER TABLE

When a table has been created, to create a UNIQUE constraint in the id_p column, use the following SQL:

• Mysql/sol Server/oracle/ms Access:

ALTER TABLE Persons
ADD UNIQUE (Id_P)

• To name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

(4) revoke a UNIQUE constraint

To revoke a UNIQUE constraint, use the following SQL:

• Mysql:

ALTER TABLE Persons
DROP INDEX uc_PersonID

• SOL Server/oracle/ms Access:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

SQL Basics (3)