SQL Tutorial

SQL - Wildcard Operators

1 The percent sign (%) Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character. 2 The underscore (_) Matches one character. Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

SQL Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data. Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order. Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index.

Integrity Constraints

Integrity constraints are used to ensure accuracy and consistency of the data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above.

SQL - Using Joins

SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol. There are different types of joins available in SQL − INNER JOIN − returns rows when there is a match in both tables. LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table. FULL JOIN − returns rows when there is a match in one of the tables. SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. CARTESIAN JOIN − returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement.

Various Syntax in SQL

SQL SELECT Statement SELECT column1, column2....columnN FROM table_name; SQL DISTINCT Clause SELECT DISTINCT column1, column2....columnN FROM table_name; SQL WHERE Clause SELECT column1, column2....columnN FROM table_name WHERE CONDITION; SQL AND/OR Clause SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2; SQL IN Clause SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N); SQL BETWEEN Clause SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2; SQL LIKE Clause SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN }; SQL ORDER BY Clause SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC}; SQL GROUP BY Clause SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name; SQL COUNT Clause SELECT COUNT(column_name) FROM table_name WHERE CONDITION; SQL HAVING Clause SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition); SQL CREATE TABLE Statement CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); SQL DROP TABLE Statement DROP TABLE table_name; SQL CREATE INDEX Statement CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN); SQL DROP INDEX Statement ALTER TABLE table_name DROP INDEX index_name; SQL DESC Statement DESC table_name; SQL TRUNCATE TABLE Statement TRUNCATE TABLE table_name; SQL ALTER TABLE Statement ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype}; SQL ALTER TABLE Statement (Rename) ALTER TABLE table_name RENAME TO new_table_name; SQL INSERT INTO Statement INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN); SQL UPDATE Statement UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ]; SQL DELETE Statement DELETE FROM table_name WHERE {CONDITION}; SQL CREATE DATABASE Statement CREATE DATABASE database_name; SQL DROP DATABASE Statement DROP DATABASE database_name; SQL USE Statement USE database_name; SQL COMMIT Statement COMMIT; SQL ROLLBACK Statement ROLLBACK;

SQL

SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. This tutorial will give you a quick start to SQL. It covers most of the topics required for a basic understanding of SQL and to get a feel of how it works.

SQL - Syntax

SQL is followed by a unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax. All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). The most important point to be noted here is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database.

SQL - Distinct Keyword

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records. There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only those unique records instead of fetching duplicate records. SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]

SQL - UNIONS CLAUSE

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use this UNION clause, each SELECT statement must have The same number of columns selected The same number of column expressions The same data type and Have them in the same order But they need not have to be in the same length.

UNION ALL Clause

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to the UNION clause will apply to the UNION ALL operator. There are two other clauses (i.e., operators), which are like the UNION clause. SQL INTERSECT Clause − This is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. SQL EXCEPT Clause − This combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

Data Integrity

The following categories of data integrity exist with each RDBMS − Entity Integrity − There are no duplicate rows in a table. Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values. Referential integrity − Rows cannot be deleted, which are used by other records. User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity.

What is a field?

A field is a cell in a table. Each field holds an individual piece of data. Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a column in a table that is designed to maintain specific information about every record in the table.

SQL - Having Clause

The part of a SQL SELECT statement that specifies conditions used to determine which rows are in the groupings in a GROUP BY clause. HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

SQL constraints

"Not Null, Unique, Default, and Check" Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table. Following are some of the most commonly used constraints available in SQL − NOT NULL Constraint − Ensures that a column cannot have a NULL value. DEFAULT Constraint − Provides a default value for a column when none is specified. UNIQUE Constraint − Ensures that all the values in a column are different. PRIMARY Key − Uniquely identifies each row/record in a database table. FOREIGN Key − Uniquely identifies a row/record in any another database table. CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions. INDEX − Used to create and retrieve data from the database very quickly.

What is a NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is the one that has been left blank during a record creation.

What is a column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

What is a Record or a Row?

A record is also called as a row of data is each individual entry that exists in a table.

Dropping Constraints

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command. ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK; Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command. ALTER TABLE EMPLOYEES DROP PRIMARY KEY; Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.

SQL - Constraints

Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table. Following are some of the most commonly used constraints available in SQL. These constraints have already been discussed in SQL - RDBMS Concepts chapter, but it's worth to revise them at this point. NOT NULL Constraint − Ensures that a column cannot have NULL value. DEFAULT Constraint − Provides a default value for a column when none is specified. UNIQUE Constraint − Ensures that all values in a column are different. PRIMARY Key − Uniquely identifies each row/record in a database table. FOREIGN Key − Uniquely identifies a row/record in any of the given database table. CHECK Constraint − The CHECK constraint ensures that all the values in a column satisfies certain conditions. INDEX − Used to create and retrieve data from the database very quickly. Constraints can be specified when a table is created with the CREATE TABLE statement or you can use the ALTER TABLE statement to create constraints even after the table is created.

Database Normalization

Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process − Eliminating redundant data, for example, storing the same data in more than one table. Ensuring data dependencies make sense. Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure. Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form. It is your choice to take it further and go to the fourth normal form, fifth normal form and so on, but in general, the third normal form is more than enough. First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) "The key, the whole key, and nothing but the key, so help me Codd."