SQL

CITATION IN BUTTON

SQL

DEFINITION

SQL (Structured Query Language)

Jessica Sirkin

SQL (Structured Query Language) is a standardized programming language that's used to manage relational databases and perform various operations on the data in them. Initially created in the 1970s, SQL is regularly used not only by database administrators, but also by developers writing data integration scripts and data analysts looking to set up and run analytical queries.

The uses of SQL include modifying database table and index structures; adding, updating and deleting rows of data; and retrieving subsets of information from within a database for transaction processing and analytics applications. Queries and other SQL operations take the form of commands written as statements -- commonly used SQL statements include select, add, insert, update, delete, create, alter and truncate.

SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s. Also known as SQL databases, relational systems comprise a set of tables containing data in rows and columns. Each column in a table corresponds to a category of data -- for example, customer name or address -- while each row contains a data value for the intersecting column.

SQL standard and proprietary extensions

An official SQL standard was adopted by the American National Standards Institute (ANSI) in 1986 and then by the International Organization for Standardization, known as ISO, in 1987. More than a half-dozen joint updates to the standard have been released by the two standards development bodies since then; as of this writing, the most recent version is SQL:2011, approved that year.

Both proprietary and open source relational database management systems built around SQL are available for use by organizations. They include:

However, many of these database products support SQL with proprietary extensions to the standard language for procedural programming and other functions. For example, Microsoft offers a set of extensions called Transact-SQL (T-SQL), while Oracle's extended version of the standard is PL/SQL. As a result, the different variants of SQL offered by vendors aren't fully compatible with one another.

SQL commands and syntax

SQL commands are divided into several different types, among them data manipulation language (DML) and data definition language (DDL) statements, transaction controls and security measures. The DML vocabulary is used to retrieve and manipulate data, while DDL statements are for defining and modifying database structures. The transaction controls help manage transaction processing, ensuring that transactions are either completed or rolled back if errors or problems occur. The security statements are used to control database access as well as to create user roles and permissions.

SQL syntax is the coding format used in writing statements. Figure 1 shows an example of a DDL statement written in Microsoft's T-SQL to modify a database table in SQL Server 2016:

Figure 1. An example of T-SQL code in SQL Server 2016. This is the code for the ALTER TABLE WITH (ONLINE = ON | OFF) option.

SQL-on-Hadoop tools

SQL-on-Hadoop query engines are a newer offshoot of SQL that enable organizations with big data architectures built around Hadoop systems to take advantage of it instead of having to use more complex and less familiar languages -- in particular, the MapReduce programming environment for developing batch processing applications.

More than a dozen SQL-on-Hadoop tools have become available through Hadoop distribution providers and other vendors; many of them are open source software or commercial versions of such technologies. In addition, the Apache Spark processing engine, which is often used in conjunction with Hadoop, includes a Spark SQL module that similarly supports SQL-based programming.

In general, SQL-on-Hadoop is still an emerging technology, and most of the available tools don't support all of the functionality offered in relational implementations of SQL. But they're becoming a regular component of Hadoop deployments as companies look to get developers and data analysts with SQL skills involved in programming big data applications.

This was last updated in December 2020

https://www.sqltutorial.org/sql-cheat-sheet/

https://www.sqltutorial.org/sql-cheat-sheet/

Querying data from a table

Query data in columns c1, c2 from a table

SELECT c1, c2 FROM t;

Code language: SQL (Structured Query Language) (sql)

Query all rows and columns from a table

SELECT * FROM t;

Code language: SQL (Structured Query Language) (sql)

Query data and filter rows with a condition

SELECT c1, c2 FROM t

WHERE condition;

Code language: SQL (Structured Query Language) (sql)

Query distinct rows from a table

SELECT DISTINCT c1 FROM t

WHERE condition;

Code language: SQL (Structured Query Language) (sql)

Sort the result set in ascending or descending order

SELECT c1, c2 FROM t

ORDER BY c1 ASC [DESC];

Code language: SQL (Structured Query Language) (sql)

Skip offset of rows and return the next n rows

SELECT c1, c2 FROM t

ORDER BY c1

LIMIT n OFFSET offset;

Code language: SQL (Structured Query Language) (sql)

Group rows using an aggregate function

SELECT c1, aggregate(c2)

FROM t

GROUP BY c1;

Code language: SQL (Structured Query Language) (sql)

Filter groups using HAVING clause

SELECT c1, aggregate(c2)

FROM t

GROUP BY c1

HAVING condition;

Code language: SQL (Structured Query Language) (sql)

Querying from multiple tables

Inner join t1 and t2

SELECT c1, c2

FROM t1

INNER JOIN t2 ON condition;

Code language: SQL (Structured Query Language) (sql)

Left join t1 and t1

SELECT c1, c2

FROM t1

LEFT JOIN t2 ON condition;

Code language: SQL (Structured Query Language) (sql)

Right join t1 and t2

SELECT c1, c2

FROM t1

RIGHT JOIN t2 ON condition;

Code language: SQL (Structured Query Language) (sql)

Perform full outer join

SELECT c1, c2

FROM t1

FULL OUTER JOIN t2 ON condition;

Code language: SQL (Structured Query Language) (sql)

Produce a Cartesian product of rows in tables

SELECT c1, c2

FROM t1

CROSS JOIN t2;

Code language: SQL (Structured Query Language) (sql)

Another way to perform cross join

SELECT c1, c2

FROM t1, t2;

Code language: SQL (Structured Query Language) (sql)

Join t1 to itself using INNER JOIN clause

SELECT c1, c2

FROM t1 A

INNER JOIN t1 B ON condition;

Code language: SQL (Structured Query Language) (sql)

Using SQL Operators

Combine rows from two queries

SELECT c1, c2 FROM t1

UNION [ALL]

SELECT c1, c2 FROM t2;

Code language: SQL (Structured Query Language) (sql)

Return the intersection of two queries

SELECT c1, c2 FROM t1

INTERSECT

SELECT c1, c2 FROM t2;

Code language: SQL (Structured Query Language) (sql)

Subtract a result set from another result set

SELECT c1, c2 FROM t1

MINUS

SELECT c1, c2 FROM t2;

Code language: SQL (Structured Query Language) (sql)

Query rows using pattern matching %, _

SELECT c1, c2 FROM t1

WHERE c1 [NOT] LIKE pattern;

Code language: SQL (Structured Query Language) (sql)

Query rows in a list

SELECT c1, c2 FROM t

WHERE c1 [NOT] IN value_list;

Code language: SQL (Structured Query Language) (sql)

Query rows between two values

SELECT c1, c2 FROM t

WHERE c1 BETWEEN low AND high;

Code language: SQL (Structured Query Language) (sql)

Check if values in a table is NULL or not

SELECT c1, c2 FROM t

WHERE c1 IS [NOT] NULL;

Code language: SQL (Structured Query Language) (sql)

Managing tables

Create a new table with three columns

CREATE TABLE t (

id INT PRIMARY KEY,

name VARCHAR NOT NULL,

price INT DEFAULT 0

);

Code language: SQL (Structured Query Language) (sql)

Delete the table from the database

DROP TABLE t ;

Code language: SQL (Structured Query Language) (sql)

Add a new column to the table

ALTER TABLE t ADD column;

Code language: SQL (Structured Query Language) (sql)

Drop column c from the table

ALTER TABLE t DROP COLUMN c ;

Code language: SQL (Structured Query Language) (sql)

Add a constraint

ALTER TABLE t ADD constraint;

Code language: SQL (Structured Query Language) (sql)

Drop a constraint

ALTER TABLE t DROP constraint;

Code language: SQL (Structured Query Language) (sql)

Rename a table from t1 to t2

ALTER TABLE t1 RENAME TO t2;

Code language: SQL (Structured Query Language) (sql)

Rename column c1 to c2

ALTER TABLE t1 RENAME c1 TO c2 ;

Code language: SQL (Structured Query Language) (sql)

Remove all data in a table

TRUNCATE TABLE t;

Code language: SQL (Structured Query Language) (sql)

Using SQL constraints

Set c1 and c2 as a primary key

CREATE TABLE t(

c1 INT, c2 INT, c3 VARCHAR,

PRIMARY KEY (c1,c2)

);

Code language: SQL (Structured Query Language) (sql)

Set c2 column as a foreign key

CREATE TABLE t1(

c1 INT PRIMARY KEY,

c2 INT,

FOREIGN KEY (c2) REFERENCES t2(c2)

);

Code language: SQL (Structured Query Language) (sql)

Make the values in c1 and c2 unique

CREATE TABLE t(

c1 INT, c1 INT,

UNIQUE(c2,c3)

);

Code language: SQL (Structured Query Language) (sql)

Ensure c1 > 0 and values in c1 >= c2

CREATE TABLE t(

c1 INT, c2 INT,

CHECK(c1> 0 AND c1 >= c2)

);

Code language: SQL (Structured Query Language) (sql)

Set values in c2 column not NULL

CREATE TABLE t(

c1 INT PRIMARY KEY,

c2 VARCHAR NOT NULL

);

Code language: SQL (Structured Query Language) (sql)

Modifying Data

Insert one row into a table

INSERT INTO t(column_list)

VALUES(value_list);

Code language: SQL (Structured Query Language) (sql)

Insert multiple rows into a table

INSERT INTO t(column_list)

VALUES (value_list),

(value_list), …;

Code language: SQL (Structured Query Language) (sql)

Insert rows from t2 into t1

INSERT INTO t1(column_list)

SELECT column_list

FROM t2;

Code language: SQL (Structured Query Language) (sql)

Update new value in the column c1 for all rows

UPDATE t

SET c1 = new_value;

Code language: SQL (Structured Query Language) (sql)

Update values in the column c1, c2 that match the condition

UPDATE t

SET c1 = new_value,

c2 = new_value

WHERE condition;

Code language: SQL (Structured Query Language) (sql)

Delete all data in a table

DELETE FROM t;

Code language: SQL (Structured Query Language) (sql)

Delete subset of rows in a table

DELETE FROM t

WHERE condition;

Code language: SQL (Structured Query Language) (sql)

Managing Views

Create a new view that consists of c1 and c2

CREATE VIEW v(c1,c2)

SELECT c1, c2

FROM t;

Code language: SQL (Structured Query Language) (sql)

Create a new view with check option

CREATE VIEW v(c1,c2)

SELECT c1, c2

FROM t;

WITH [CASCADED | LOCAL] CHECK OPTION;

Code language: SQL (Structured Query Language) (sql)

Create a recursive view

CREATE RECURSIVE VIEW v

select-statement -- anchor part

UNION [ALL]

select-statement; -- recursive part

Code language: SQL (Structured Query Language) (sql)

Create a temporary view

CREATE TEMPORARY VIEW v

SELECT c1, c2

FROM t;

Code language: SQL (Structured Query Language) (sql)

Delete a view

DROP VIEW view_name;

Code language: SQL (Structured Query Language) (sql)

Managing indexes

Create an index on c1 and c2 of the t table

CREATE INDEX idx_name

ON t(c1,c2);

Code language: SQL (Structured Query Language) (sql)

Create a unique index on c3, c4 of the t table

CREATE UNIQUE INDEX idx_name

ON t(c3,c4)

Code language: SQL (Structured Query Language) (sql)

Drop an index

DROP INDEX idx_name;

Code language: SQL (Structured Query Language) (sql)

Managing triggers

Create or modify a trigger

CREATE OR MODIFY TRIGGER trigger_name

WHEN EVENT

ON table_name TRIGGER_TYPE

EXECUTE stored_procedure;

Code language: SQL (Structured Query Language) (sql)

WHEN

BEFORE – invoke before the event occurs
AFTER – invoke after the event occurs

EVENT

INSERT – invoke for INSERT
UPDATE – invoke for UPDATE
DELETE – invoke for DELETE

TRIGGER_TYPE

FOR EACH ROW
FOR EACH STATEMENT

Delete a specific trigger

DROP TRIGGER trigger_name;

https://www.sqltutorial.org/sql-cheat-sheet/

CONFIDENTIALITY / COPYRIGHT NOTICE: This site and my information and any attachments contains the PRIVILEGED AND CONFIDENTIAL INFORMATION of Fred Finkelstein & Irondesigner DBA / LLC mutually, Inc., its affiliated corporations or legal entities, and is intended only for the use of the individual(s) named above. If you are not the intended recipient of this e-mail or invited to this site, or the employee or agent responsible for delivering this to the intended recipient, you are hereby notified that any unlawful interception, dissemination, disclosure, printing or copying of this e-mail or site or any attachments is strictly prohibited under the Electronics Communication Privacy Act (ECPA), 18 USCA 2510, 18 USCA 2511, and any applicable laws. If you have received this e-mail or viewing this site in error, please delete or destroy it & close down, including all attachments or copies, and immediately notify us by e-mail at mechanicalengrg@gmail.com

Page updated

Google Sites

Report abuse