651 - 700 Flashcards
SQL.TRUNCATE
command deletes the data inside a table, but not the table itself.
TRUNCATE TABLE Categories;
SQL.Views
🎯 CREATE VIEW - create view
🎯CREATE OR REPLACE VIEW
🎯 DROP VIEW - view is deleted
is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table.
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = 'Brazil';
DROP VIEW [Brazil Customers];
SQL.CHECK
constraint limits the value that can be placed in a column.
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is
created. The CHECK constraint ensures that you can not have any person below 18 years:
CREATE TABLE Persons (
Age int,
CHECK (Age>=18)
);
CREATE TABLE Persons (
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL.REPLACE()
replaces all occurrences of a substring within a string, with a new substring.
SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML');
SELECT REPLACE('ABC ABC ABC', 'a', 'c');
SELECT REPLACE('ABC ABC ABC', 'a', 'c');
SQL.UNIQUE
constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
ALTER TABLE Persons ADD UNIQUE (ID);
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
SQL.DESC
command is used to sort the data returned in descending order.
SELECT * FROM Customers ORDER BY CustomerName DESC;
SQL.ASC
command is used to sort the data returned in ascending order.
SELECT * FROM Customers ORDER BY CustomerName ASC;
SQL.IN
operator allows you to specify multiple values in a WHERE clause.
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
SQL.DEFAULT
constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'Sandnes';
SQL.MIN() and SQL.MAX()
function returns the smallest or largest value of the selected column.
SELECT MIN(Price) AS SmallestPrice FROM Products;
SELECT MAX(Price) AS LargestPrice FROM Products;
SQL.ADD
command is used to add a column in an existing table.
The following SQL adds an "Email" column to the "Customers" table: ALTER TABLE Customers ADD Email varchar(255);
SQL.INSERT INTO
statement is used to insert new records in a table.
1. Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows: INSERT INTO table_name VALUES (value1, value2, value3, ...);
The following SQL statement inserts a new record in the "Customers" table:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
SQL.FOREIGN KEY
constraint is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders"
table is created:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
SQL.PRIMARY KEY
constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key.
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
LTER TABLE Persons ADD PRIMARY KEY (ID);
ALTER TABLE Persons DROP PRIMARY KEY;
SQL.ADD CONSTRAINT(Ограничение)
command is used to create a constraint(ограничение) after a table is already created.
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
ALTER TABLE Persons DROP CONSTRAINT UC_Person;
ALTER TABLE Persons DROP CONSTRAINT PK_Person;
ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
SQL.HAVING
command is used instead of WHERE with aggregate functions.
The following SQL lists the number of customers in each country. Only include countries with more than 5 customers: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
SQL.USING
можно использовать если имена колонок из 2 таблиц совпадают для соединения таблиц.
SELECT l.name AS language, c.name AS country FROM languages AS l INNER JOIN countries AS c USING(code) ORDER BY language;
SELECT e.EMPLOYEE_ID, e.LAST_NAME, d.LOCATION_ID FROM Employees e JOIN Departments d USING(DEPARTMENT_ID);
SELECT l.location_id, l.street_address, l.postal_code, c.country_name FROM locations l JOIN countries c USIN(country_id);
SQL.SUM()
function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name WHERE condition;
SQL.COUNT()
function returns the number of rows that matches a specified criterion.
SELECT COUNT(column_name) FROM table_name WHERE condition;
SQL.AVG()
function returns the average value of a numeric column.
SELECT AVG(column_name) FROM table_name WHERE condition;
SQL.UPDATE
statement is used to modify the existing records in a table.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city. UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
SQL.DELETE
statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
SQL.CREATE TABLE
statement is used to create a new table in a database.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;
SQL.DROP
statement is used to drop an existing table column in a database.
ALTER TABLE Customers DROP COLUMN ContactName;
ALTER TABLE Persons DROP CONSTRAINT UC_Person;
DROP TABLE Shippers;
SQL.ALTER TABLE
the statement is used to add, delete, modify columns or to add and drop various constraints on an existing table.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE Customers ADD Email varchar(255);
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name ALTER COLUMN column_name datatype;
SQL.CREATE DATABASE
SQL.INTERSECT
In simple words, the INTERSECT statement will return only those rows which will be common to both of the SELECT statements.
SELECT column1 , column2 .... FROM table_names WHERE condition INTERSECT SELECT column1 , column2 .... FROM table_names WHERE condition
SQL.EXCEPT
clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] EXCEPT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
SQL.UNION and SQL.UNION ALL
operator is used to combine the result-set of two or more SELECT statements.operator is used to combine the result-set of two or more SELECT statements. UNION ALL = allow duplicate values
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
SQL.DISTINCT
return only distinct (different) values.
SELECT DISTINCT column1, column2, ... FROM table_name;
SELECT DISTINCT Country FROM Customers;
SELECT COUNT(DISTINCT Country) FROM Customers;
SQL.SELECT.TOP
SQL.OFFSET
clause specifies the number of rows to skip before starting to return rows from the query.
SELECT * FROM artists LIMIT 5 OFFSET [Number of rows to skip];
SELECT * FROM artists LIMIT 5 OFFSET 2;
SQL.LIMIT
clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
SELECT * FROM Customers LIMIT 3;
SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
SQL.FETCH
clause specifies the number of rows to return after the OFFSET clause has been processed.
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price,
product_name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC,
product_name
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
SQL.PIVOT and SQL.Unpivot
operators that are used to transform one table into another in order to achieve more simpler view of table. Conventionally we can say that Pivot operator converts the rows data of the table into the column data.
SELECT (ColumnNames) FROM (TableName) PIVOT ( AggregateFunction(ColumnToBeAggregated) FOR PivotColumn IN (PivotColumnValues) ) AS (Alias) //Alias is a temporary name for a table
SELECT (ColumnNames) FROM (TableName) UNPIVOT ( AggregateFunction(ColumnToBeAggregated) FOR PivotColumn IN (PivotColumnValues) ) AS (Alias)
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION FROM geeksforgeeks PIVOT ( SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION ) ) AS PivotTable
SQL.CAST(expr AS type) and SQL.CONVERT(expr, type)
принимает выражение expr любого типа и возвращает результирующее значение указанного типа type.
SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR); 👉 1944, 1945 SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR); 👉 2066, 2067
SELECT CONVERT('HI', CHAR CHARACTER SET utf8mb4);
SQL.SUBSTRING and SQL.SUBSTR and SQL.MID(string, start, length)
extracts a substring from a string (starting at any position).
SELECT SUBSTRING("SQL Tutorial", 5, 3) AS ExtractString;
SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString FROM Customers;
SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;
SQL.REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
заменяет вхождения в строке expr, которые соответствуют регулярному выражению, заданному шаблоном pat, строкой замены repl, и возвращает результирующую строку.
SELECT REGEXP_REPLACE('a b c', 'b', 'X');
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
SQL.REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])
возвращает подстроку строки expr, совпадающую с регулярным выражением, указанным в шаблоне pat, NULL, если совпадений нет.
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+')
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
SQL.REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
возвращает начальный индекс подстроки строки expr, соответствующей регулярному выражению, заданному шаблоном pat, и возвращает 0, если совпадений нет.
SELECT REGEXP_INSTR('dog cat dog', 'dog');
SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
SQL.MEDIUMINT(size)
A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215.
SQL.INT(size) and SQL.INTEGER(size)
A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295.
SQL.SMALLINT(size)
A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535.
SQL.TINYINT(size)
A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255.
SQL.BIT(size)
A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64.
SQL.ENUM(val1, val2, val3, …)
A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list.
SQL.SET(val1, val2, val3, …)
A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list
…
SET @a = (SELECT MIN(lat_n) FROM station);
SET @b = (SELECT MAX(lat_n) FROM station);
SET @c = (SELECT MIN(long_w) FROM station);
SET @d = (SELECT MAX(long_w) FROM station);
SET @distance = ABS(@a - @b) + ABS(@c - @d);
SELECT FORMAT(@distance, 4);
…
SQL.SET(val1, val2, val3, …)
A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list
SQL.TIME(fsp)
A time. Format: hh:mm:ss. The supported range is from ‘-838:59:59’ to ‘838:59:59’
SQL.TIMESTAMP()
A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.
