SQL Fundamentals

Basic Concepts

Introdction

A Database

A database is a collection of data that is organized in a manner that facilitates ease of access, as well as efficient management and updating.

A database is made up of tables that store relevant information.

For example, you would use a database, if you were to create a website like YouTube, which contains a lot of information like videos, usernames, passwords, comments.

A database is a collection of data.

Database Tables

A table stores and displays data in a structured format consists of columns and rows that are smilar to those in Excel spreadsheets.

Databases often contain multiple tables, each designed for a specific purpose. For example, imagine creating a database table of names and telephone numbers.

Each table includes its own set of fields, based on the data it will store.

A table has a specified number of columns but can have any number of rows.

Primary Keys

A primary key is a field in the table that uniquely identifies the table records.

The primary key’s main features: - It must contain a unique value for each row. - It cannot contain NULL values.

For example, our table contains a record for each name in a phone book. The unique ID number would be a good choice for a primary key in the table, as there is always the chance for more than one person to have the same name.

- Tables are limited to ONE primary key each. - The primary key’s value must be different for each row.

What is SQL?

Once you understand what a database is, understanding SQL is easy. SQL stands for Structured Query Language.

SQL is used to access and manipulate a database. MySQL is a program that understands SQL.

SQL can: - insert, update, or delete records in a database. - create new databases, table, stored procedures, views. - retrieve data from a database, etc.

SQL stands for Structured Query Language.

SQL is an ANSI (American National Standards Institute) standard, but there are different versions of the SQL language. Most SQL database programs have their own proprietary extensions in addition to the SQL standard, but all of them support the major commands.

SQL Statements: SELECT

Basic SQL Commands

SHOW DATABASES

The SQL SHOW statement displays information contained in the database and its tables. This helpful tool lets you keep track of your database contents and remind yourself about the structure of your tables.

For example, the SHOW DATABASES command lists the databases managed by the server.

SHOW DATABASES

SHOW DATABASES returns a list of all databases on the server.

SHOW TABLES

The SHOW TABLES command is used to display all of the tables in the currently selected MySQL database.

SHOW TABLES returns a list of all tables in the database

SHOW COLUMNS

SHOW COLUMNS displays information about the columns in a given table.

The following example displays the columns in our customers table:

SHOW COLUMNS FROM customers

A table is somehow represents an object.

A database represents a project.

SHOW COLUMNS displays the following values for each table column:

Field: column name Type: column data type Key: indicates whether the column is indexed Default: default value assigned to the column Extra: may contain any additional information that is available about a given column

SELECT Statement

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

A query may retrieve information from selected columns or from all columns in the table. To create a simple SELECT statement, specify the name(s) of the column(s) you need from the table.

Syntax of the SQL SELECT Statement:

SELECT column_list
FROM table_name

- column_list includes one or more columns from which data is retrieved - table-name is the name of the table from which the information is retrieved

Below is the data from our customers table:

The following SQL statement selects the FirstName from the customers table:

SELECT FirstName FROM customers

A SELECT statement retrieves zero or more rows from one or more database tables.

SELECT is used to select a data from database. The result is stored in a reslt table, to get the result we need to perform a query.

This is somehow like a function, it returns some results, and we saved the result into some variables and when we need the result to show, we still need to print or do something.

def sqrt(x):
  return x*x

result = sqrt(5) # result table , result-set
print(result) # query 

SQL Syntax Rules

Multiple Queries

SQL allows to run multiple queries or commands at the same time.

The following SQL statement selects the FirstName and City columns from the customers table:

SELECT FirstName FROM customers;
SELECT city FROM customers;

Remember to end each SQL statement with a semicolon to indicate that the statement is complete and ready to be interpreted. In this tutorial, we will use semicolon at the end of each SQL statement.

Case Sensitivity

SQL is case insensitive.

SQL is case insensitive

The following statements are equivalent and will produce the same result:

select city from customers;
SELECT City From customers;
SeLEct City from customers;

It is common practice to write all SQL commands in upper-case.

Syntax Rules

A single SQL statement can be placed on one or more text lines. In addition, multiple SQL statements can be combined on a single text line.

White spaces and multiple lines are ignored in SQL. For example, the following query is absolutely correct.

1. SQL statement can take up one or multiple line
2. White spaces and multiple lines are ignored.

SELECT City
FROM customers;

However, it is recommended to avoid unnecessary white spaces and lines.

Combined with proper spacing and indenting, breaking up the commands into logical lines will make your SQL statements much easier to read and maintain.

Selecting Multiple Columns

Selecting multiple columns

As previously mentioned, the SQL SELECT statement retrieves records from tables in your SQL database.

You can select multiple table columns at once. Just list the column names, separated by commas:

SELECT FirstName, LastName, City
FROM customers;

Do not put a comma after the last column name.

It is a one query statement. Not two, so we are not going to have comma or something else, that will break the code may encourage syntax errors.

Selecting All Columns

To retrieve all of the information contained in your table, place an asterisk (*) sign after the SELECT command, rather than typing in each column names separately.

The following SQL statement selects all of the columns in the customers table:

SELECT * FROM customers;

In SQL, the asterisk means all.

Actually not only in SQL, in many other languages it is means all. I think it is used from the regular expression where the * means all.

DISTINCT and LIMIT

The DISTINCT Keyword

In situations in which you have multiple duplicate records in a table, it might make more sense to return only unique records, instead of fetching the duplicates.

The SQL DISTINCT keyword is used in conjunction with SELECT to eliminate all duplicate records and return only unique ones.

1. DISTINCT keyword is using conjunction with SELECT.
2. It only returns unique values.

DISTINCT is used to return only distinct (different) values.

SELECT DISTINCT column_name1, column_name2 FROM table_name;

The LIMIT keyword

By default, all results that satisfy the conditions specified in the SQL statement are returned. However, sometimes we need to retrieve just a subset of records. In MySQL, this is accomplished by using the LIMIT keyword.

The syntax for LIMIT is as follows:

SELECT column list
FROM table_name
LIMIT [number of records];

For example, we can retrieve the first five records from the customers table.

SELECT ID, FirstName, LastName, City
FROM customers LIMIT 5;

By default, all results that satisfy the conditions specified in the SQL statement are returned.

Pickup a setof records from a particular offset using LIMIT

You can also pick up a set of records from a particular offset. In the following example, we pick up four records, starting from the 3 position:

SELECT ID, FirstName, LastName, City
FROM customers LIMIT 3,4;

This would return four records starting from the third position.

1. Mysql Starts counting from there, so, the position 3 actually is ID 4
2. The reason that it produces results starting from ID number four, and not three, is that MySQL starts counting from zero, meaning that the offset of the first row is 0, not 1

Sorting Results

Order By

ORDER BY is used to sort the result-set

ORDER BY is used with SELECT to sort the returned data.

The following example sorts our customers table by the FirstName column.

SELECT * FROM customers
ORDER BY firstName;

As you can see, the rows are ordered alphabetically by the FirstName column.

By default, the ORDER BY keyword sorts the results in ascending order.

Sorting Multiple Columns

ORDER BY can sort retrieved data by multiple columns. When using ORDER BY with more than one column, separate the list of columns to follow ORDER BY with commas. Here is the customers table, showing the following records:

To oeder by LastName and Age:

SELECT * FROM customers
ORDER BY LastName, Age;

This ORDER BY statement returns the following result:

As we have two Smiths, they will be ordered by the Age column in ascending order.

The ORDER BY command starts ordering in the same sequence as the columns. It will order by the first column listed, then by the second, and so on.

Filtering, Functions, Subqueries

The WHERE Statement

WHERE

WHERE. is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified criterion.

The syntax for the WHERE clause:

SELECT column_list
FROM table_name
WHERE condition;

In the above table, to SELECT a specific record:

SELECT * FROM customers
WHERE ID=7;

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL Operators

Comparison Operators and Logical Operators are used in the WHERE clause to filter the data to be selected.

The following cmparison operators can be used in the WHERE clause:

inclusive range image

For example, we can display all customers names listed in our table, with the exception of the one with ID 5.

SELECT * FROM customers WHERE ID!=5;

As you can see, the record with ID=5 is excluded from the list.

The BETWEEN Operator

The BETWEEN operator selects values within a range. The first value must be lower bound and the second value, the upper bound.

The syntax for the BETWEEN clause is as follows:

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

The following SQL statement selects all records with IDs that fall between 3 and 7:

the lower bound and upper bound are both included in the range.

Text Values

When working with text columns, surround any text that appears in the statement with single quotation marks (‘).

The following SQL statement selects all records in which the City is equal to ‘New York’.

SELECT ID, FirstName, LastName, City FROM customers WHERE City='New York';

If your text contains an apostrophe (single quote), you should use two single quote characters to escape the apostrophe. For example: ‘Can’‘t’.

‘’ Means ‘ when inside the ‘ and ’

Filtering with AND, OR

Logical Operators

Logical operators can be used to combine two Boolean values and return a result of true, false, or null. The following operators can be used:

When retrieving data using a SELECT statement, use logical operators in the WHERE clause to combine multiple conditions.

AND

If you want to select rows that satisfy all of the given conditions, use the logical operator, AND.

To find the names of the customers between 30 to 40 years of age, set up the query as seen here:

SELECT ID< FirstName, LastName, Age
FROM customers WHERE Age>=30 AND Age <=40

This results in the following output:

You can combine as many conditions as needed to return the desired results.

OR

If you want to select rows that satisfy at least one of the given conditions, you can use the logical OR operator.

For example, if you want to find the customers who live either in New York or Chicago, the query would look like this:

SELECT * FROM customers WHERE City = 'New York' OR City = 'Chicago';

Combining AND & OR

The SQL AND and OR conditions may be combined to test multiple conditions in a query. These two operators are called conjunctive operators.

When combining these conditions, it is important to use parentheses, so that the order to evaluate each condition is known.

1. AND and OR Operands canbe combined.
2. They are called conjunctive operands
3. use parantheses () when using conjecntive operands

The statement below selects all customers from the city “New York” AND with the age equal to “30” OR “35”:

SELECT * FROM customers WHERE City = 'New York' AND (Age =30 OR Age =35);

IN, NOT IN Statement

The IN Operator

The IN operator is used when you want to compare a column with more than one value.

For example, you might need to select all customers from New York, Los Angeles, and Chicago. With the OR condition, your SQL would look like this:

SELECT * FROM customers WHERE City='New York' OR City='Los Angeles' OR City='Chicago';

ou can achieve the same result with a single IN condition, instead of the multiple OR conditions:

SELECT * FROM customers
WHERE City IN ('New York', 'Los Angeles', 'Chicago');

This would also produce the same result.

Note the use of parentheses in the syntax.

Select customers from NY, CA, or NC, using the IN statement.

NOT IN Operator

The NOT IN operator allows you to exclude a list of specific values from the result set.

If we add the NOT keyword before IN in our previous query, customers living in those cities will be excluded:

SELECT * FROM customers WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago');

The NOT IN operator allows you to exclude a list of specific values from the result set.

Custom Columns

The CONCAT Function

The CONCAT function is used to concatenate two or more text values and returns the concatenating string.

Let’s concatenate the FirstName with the City, separating them with a comma:

SELECT CONCAT (FirstName,',',City) FROM customers;

The CONCAT() function takes two or more parameters.

The AS keyword

SELECT CONCAT(FirstName,', ', City) AS new_column 
FROM customers;

nd when you run the query, the column name appears to be changed.

A concatenation results in a new column.

Arithmetic Operators

Arithmetic operators perform arithmetical operations on numeric operands. The Arithmetic operators include addition (+), subtraction (-), multiplication (*) and division (/).

The following employees table shows employee names and salaries:

The example below adds 500 to each employee’s salary and selects the result:

SELECT ID, FirstName, LastName, Salary+500 AS Salary FROM employees;

Parentheses can be used to force an operation to take priority over any other operators. They are also used to improve code readability.

Functions

The UPPER and LOWER Function

he UPPER function converts all letters in the specified string to uppercase. The LOWER function converts the string to lowercase.

The following SQL query selects all LastNames as uppercase:

SELECT FirstName, UPPER(LastName) AS LastName 
FROM employees;

If there are characters in the string that are not letters, this function will have no effect on them.

SQRT and AVG

The SQRT function returns the square root of given value in the argument.

Let’s calculate the square root of each Salary:

SELECT Salary, SQRT(Salary) 
FROM employees;

Similarly, the AVG function returns the average value of a numeric column:

SELECT AVG(Salary) FROM employees;

Another way to do the SQRT is to use POWER with the 1/2 exponent. However, SQRTseems to work faster than POWER in this case.

The SUM function

The SUM function is used to calculate the sum for a column’s values.

For example, to get the sum of all of the salaries in the employees table, our SQL query would look like this:

SELECT **SUM(Salary)** FROM employees;

Subqueries

subquery is a query within another query.

Let’s consider an example. We might need the list of all employees whose salaries are greater than the average. First, calculate the average:

SELECT AVG(Salary) FROM employees;

As we already know the average, we can use a simple WHERE to list the salaries that are greaterthan that number.

SELECT FirstName, Salary FROM employees WHERE Salary > 3100 ORDER BY Salary DESC;

The DESC keyword sorts results in descending order. Similarly, ASC sorts the results in ascending order.

A single subquery will return the same result more easily.

SELECT FirstName, Salary FROM employees WHERE Salary > (SELECT AVG(Salary) FROM employees) ORDER BY Salary DESC;

Enclose the subquery in parentheses. Also, note that there is no semicolon at the end of the subquery, as it is part of our single query.

LIKE and MIN

The Like Operator

The LIKE keyword is useful when specifying a search condition within your WHERE clause.

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters).

For example, to select employees whose FirstNames begin with the letter A, you would use the following query:

SELECT * FROM employees 
WHERE FirstName LIKE 'A%';

As another example, the following SQL query selects all employees with a LastName ending with the letter “s”:

SELECT * FROM employees 
WHERE LastName LIKE '%s';

The % wildcard can be used multiple times within the same pattern.

The MIN Function

The MIN function is used to return the minimum value of an expression in a SELECT statement.

For example, you might wish to know the minimum salary among the employees. SELECT MIN(Salary) AS Salary FROM employees;

JOIN, Table Operations

Joingning Tables

All of the queries shown up until now have selected from just one table at a time.

One of the most beneficial features of SQL is the ability to combine data from two or more tables.

In the two tables that follow, the table named customers stores information about customers:

The orders table stores information about individual orders with their corresponding amount:

In SQL Joining tables means combining data from two or more tables. A table join creates a temporary table showing the data from the joined tables.

Rather than storing the customer name in both tables, the orders table contains a reference to the customer ID that appears in the customers table. This approach is more efficient, as opposed to storing the same text values in both tables. In order to be able to select the corresponding data from both tables, we will need to join them on that condition.

To join the two tables, specify them as a comma-separated list in the FROM clause:

SELECT customers.ID, customers.Name, orders.Name, orders.Amount 
FROM customers, orders
WHERE customers.ID=orders.Customer_ID
ORDER BY customers.ID

Each table contains “ID” and “Name” columns, so in order to select the correct ID and Name, fully qualified names are used.

Note that the WHERE clause “joins” the tables on the condition that the ID from the customers table should be equal to the customer_ID of the orders table.

The returned data shows customer orders and their corresponding amount.

Types of Join

Custom Names

Custom Names can be used for tables as well. You can shorten the join statements by giving the tables “nicknames”

SELECT ct.ID, ct.Name, ord.Name, ord.Amount 
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID

As you can see, we shortened the table names as we used them in our query.

Types of Join

The following are the types of JOIN that can be used in MySQL: - INNER JOIN - LEFT JOIN - RIGHT JOIN

Inner Join

INNER JOIN is equivalent to JOIN. It returns rows when there is a match between the tables.

Syntax:

SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name=table2.column_name;

Note the ON keyword for specifying the inner join condition.

The image below demonstrates how INNER JOIN works:

Only the records matching the join condition are returned.

LEFT JOIN

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.

This means that if there are no matches for the ON clause in the table on the right, the join will still return the rows from the first table in the result.

The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM tale1 LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

The OUTER keyword is optional, and can be omitted.

The image below demonstrates how LEFT JOIN works:

Consider the following tables: customers:

items:

The following SQL statement will return all customers, and the items they might have:

SELECT customers.Name, items.Name
FROM customers LEFT JOIN items
ON customers.ID=items.Seller_id;

The result set contains all the rows from the left table and matching data from the right table.

If no match is found for a particular row, NULL is returned.

Right Join

The RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.

The basic syntax of RIGHT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name =table2.column_name;

Again, the OUTER keyword is optional, and can be omitted.

Consider the same example from our previous lesson, but this time with a RIGHT JOIN:

SELECT customers.Name, items.Name FROM customers
RIGHT JOIN items ON customers.ID=items.Seller_id;

The RIGHT JOIN returns all the rows from the right table (items), even if there are no matches in the left table (customers).

There are other types of joins in the SQL language, but they are not supported by MySQL.

UNION

Set Operation

Occasionally, you might need to combine data from multiple tables into one comprehensive dataset. This may be for tables with similar data within the same database or maybe there is a need to combine similar data across databases or even across servers.

To accomplish this, use the UNION and UNION ALL operators.

UNION combines multiple datasets into a single dataset, and removes any existing duplicates. UNION ALL combines multiple datasets into one dataset, but does not remove duplicate rows.

UNION ALL is faster than UNION, as it does not perform the duplicate removal operation over the data set.

UNION

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

All SELECT statements within the UNION must have the same number of columns. The columns must also have the same data types. Also, the columns in each SELECT statement must be in the same order. The syntax of UNION is as follows:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Here is the First of two tables:

And here is the Second:

SELECT ID, FirstName, LastName, City FROM First
UNION
SELECT ID, FirstName, LastName, City FROM Second;

As you can see, the duplicates have been removed.

TIP: If your columns don’t match exactly across all queries, you can use a NULL (or any other) value such as:

SELECT FirstName, LastName, Company FROM businessContacts
UNION
SELECT FirstName, LastName, NULL FROM otherContacts;

The UNION operator is used to combine the result-sets of two or more SELECTstatements.

UNION ALL

UNION ALL selects all rows from each table and combines them into a single table.

The following SQL statement uses UNION ALL to select data from the First and Second tables:

SELECT ID, FirstName, LastName, City FROM First
UNION ALL
SELECT ID, FirstName, LastName, City FROM Second

As you can see, the result set includes the duplicate rows as well.

The INSERT Statement

Inserting Data

SQL tables store data in rows, one row after another. The INSERT INTO statement is used to add new rows of data to a table in the database. The SQL INSERT INTO syntax is as follows:

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

Make sure the order of the values is in the same order as the columns in the table.

Consider the following Employees table:

Use the following SQL statement to insert a new row:

INSERT INTO Employees 
VALUES (8, 'Anthony', 'Young', 35);

The values are comma-separated and their order corresponds to the columns in the table. Result:

When inserting records into a table using the SQL INSERT statement, you must provide a value for every column that does not have a default value, or does not support NULL.

Alternatively, you can specify the table’s column names in the INSERT INTO statement:

INSERT INTO table_name (column1, column2, column3, …,columnN)
VALUES (value1, value2, value3,…valueN);

column1, column2, …, columnN are the names of the columns that you want to insert data into.

INSERT INTO Employees (ID, FirstName, LastName, Age) 
VALUES (8, 'Anthony', 'Young', 35);

This will insert the data into the corresponding columns:

You can specify your own column order, as long as the values are specified in the same order as the columns.

It is also possible to insert data into specific columns only.

INSERT INTO Employees (ID, FirstName, LastName) VALUES (9, ‘Samuel’, ‘Clark’);

UPDATE and DELETE Statement

Updating Data

The UPDATE statement allows us to alter data in the table.

The basic syntax of an UPDATE query with a WHERE clause is as follows:

UPDATE table_name
SET column1=value1, column2=value2,...
WHERE condition;

You specify the column and its new value in a comma-separated list after the SET keyword.

If you omit the WHERE clause, all records in the table will be updated!

Consider the following table called “Employees”:

To update John’s salary, we can use the following query:

UPDATE Employees 
SET Salary=5000
WHERE ID=1;

Updating Multiple Columns

It is also possible to UPDATE multiple columns at the same time by comma-separating them:

UPDATE Employees 
SET Salary=5000, FirstName='Robert'
WHERE ID=1;

You can specify the column order any way you like in the SET clause.

Deleting Data

The DELETE statement is used to remove data from your table. DELETE queries work much like UPDATE queries.

DELETE FROM table_name
WHERE condition;

For example, you can delete a specific employee from the table:

DELETE FROM Employees
WHERE ID=1;

1. If you omit the WHERE clause, all records in the table will be deleted!
2. The DELETE statement removes the data from the table permanently.
3. The DELETE actually deletes one record, or we call it one row.

Creating a Table

SQL Tables

A single database can house hundreds of tables, each playing its own unique role in the database schema.

SQL tables are comprised of table rows and columns. Table columns are responsible for storing many different types of data, including numbers, texts, dates, and even files.

The CREATE TABLE statement is used to create a new table.

Creating a basic table involves naming the table and defining its columns and each column’s data type.

To create a new table:

1. give a name for the table itself
2. define the table columns
3. define the each column’s data type

Creating a Table

The basic syntax for the CREATE TABLE statement is as follows:

CREATE TABLE table_name
(
  column_name1 data_type(size),
  column_name2 data_type(size),
  column_name3 data_type(size),
  ...
  column_nameN data_type(size),
);

• the column_names specify the names of the columns we want to create.
• the data type parameter specifies what type of data the column can hold. For example use int for whole numbers.
• The size parameter specifies the maximum length of the table’s column.

Note the parentheses in the syntax.

Assume that you want to create a table called “Users” that consists of four columns: UserID, LastName, FirstName, and City.

Use the following CREATE TABLE statement:

CREATE TABLE Users
(
   UserID int,
   FirstName varchar(100), 
   LastName varchar(100),
   City varchar(100)
); 

varchar is the datatype that stores characters. You specify the number of characters in the parentheses after the type. So in the example above, our fields can hold max 100 characters long text.

DataTypes

Data types specify the type of data for a particular column.

If a column called “LastName” is going to hold names, then that particular column should have a “varchar” (variable-length character) data type.

The most common data types:

• Numeric
  • INT - A normal-sized integer that can be signed or unsigned
  • FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals.
  • DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D)
• Date and Time
  • DATE - A date in YYYY-MM-DD format
  • DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format
  • TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970
  • TIME - Stores the time in HH:MM:SS format.
• String Type
  • CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
  • VARCHARM(M) - variable-lenght character string. Max size is specified in parenthesis.
  • BLOB - “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files.
  • TEXT - Large amount of text data.

Choosing the correct data type for your columns is the key to good database design.

Primary Key

The UserID is the best choice for our Users table’s primary key. Define it as a primary key during table creation, using the PRIMARY KEY keyword.

CREATE TABLE Users
(
   UserID int,
   FirstName varchar(100),
   LastName varchar(100),
   City varchar(100),
   PRIMARY KEY(UserID)
); 

Specify the column name in the parentheses of the PRIMARY KEY keyword.

Creating a Table

Now, when we run the query, our table will be created in the database.

You can now use INSERT INTO queries to insert data into the table.

NOT NULL and AUTO_INCREMENT

SQL constraints

SQL constraints are used to specify rules for table data.

constraint

The following are commonly used SQL constraints:

• NOT NULL - Indicates that a column cannot contain any NULL (An absense of value) value.
• UNIQUE - Does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
• PRIMARY KEY - Enforces the table to accept unique data for a specific column and this constraint create a unique index for accessing the table faster.
• CHECK - Determines whether the value is valid or not from a logical expression.
• DEFAULT- While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

For example, the following means that the name column disallows NULL values.

name varchar(100) NOT NULL

During table creation, specify column level constraint(s) after the data type of that column.

AUTO INCREMENT

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

Often, we would like the value of the primary key field to be created automatically every time a new record is inserted.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. Let’s set the UserID field to be a primary key that automatically generates a new value:

UserID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (UserID);

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

Using constraints

The example below demonstrates how to create a table using constraints.

CREATE TABLE Users (
id int NOT NULL AUTO_INCREMENT,
username varchar(40) NOT NULL, 
password varchar(10) NOT NULL,
PRIMARY KEY(id)
);

The following SQL enforces that the “id”, “username”, and “password” columns do not accept NULL values. We also define the “id” column to be an auto-increment primary key field.

When inserting a new record into the Users table, it’s not necessary to specify a value for the id column; a unique new value will be added automatically.

Alter, Drop, Rename a Table

ALTER TABLE

alter: change өзгерту қайтадан жасау verb.

The ALTER TABLE command is used to add, delete, or modify columns in an existing table. You would also use the ALTER TABLE command to add and drop various constraints on an existing table.

Consider the following table called People:

The following SQL code adds a new column named DateOfBirth:

ALTER TABLE People ADD DateOfBirth date;

All rows will have the default value in the newly added column, which, in this case, is NULL.

NULL :

NULL

All rows will have the default value in the newly added column, which, in this case, is NULL.

Dropping

The following SQL code demonstrates how to delete the column named DateOfBirth in the People table.

ALTER TABLE People 
DROP COLUMN DateOfBirth;

The People table will now look like this:

The column, along with all of its data, will be completely removed from the table.

To delete the entire table, use the DROP TABLE command:

DROP TABLE People;

Be careful when dropping a table. Deleting a table will result in the complete loss of the information stored in the table!

Renaming

The ALTER TABLE command is also used to rename columns:

ALTER TABLE People
CHANGE FirstName name varchar(100);

This query will rename the column called FirstName to name.

Renaming Tables

You can rename the entire table using the RENAME command:

RENAME TABLE People TO Users;

This will rename the table People to Users.

Views

In SQL, a VIEW is a virtual table that is based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Views allow us to:

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
• Summarize data from various tables and use it to generate reports.

To create a view:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

The SELECT query can be as complex as you need it to be. It can contain multiple JOINS and other commands.

Creating Views

Consider the Employees table, which contains the following records:

Let’s create a view that displays each employee’s FirstName and Salary.

CREATE VIEW List AS
SELECT FirstName, Salary
FROM  Employees;

Now, you can query the List view as you would query an actual table.

SELECT * FROM List;

A view always shows up-to-date data! The database engine uses the view’s SQL statement to recreate the data each time a user queries a view.

Updating a View

You can update a view by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

The example below updates our List view to select also the LastName:

CREATE OR REPLACE VIEW List AS
SELECT FirstName, LastName, Salary
FROM Employees;

You can delete a view with the DROP VIEW command.

DROP VIEW List;

It is sometimes easier to drop a table and recreate it instead of using the ALTER TABLEstatement to change the table’s definition.

SQL Views are updated dynamically