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;
