SQL
SQL constraints
SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint.
SQL Joins
SQL joins are used to combine rows from two or more tables. 1) Inner Joins (2) Left Join (3) Right Join (4) Full Join
NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.
FOREIGN KEY Constraint
A FOREIGN KEY in one table points to a PRIMARY KEY in another table. CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
CREATE VIEW 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. CREATE VIEW [Products Above Average Price] AS SELECT ProductName,UnitPrice FROM Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
UNIQUE Constraint on ALTER TABLE
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
Example of SQL CHECK Constraint
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
CREATE INDEX Statement
Indexes allow the database application to find data fast; without reading the whole table. CREATE INDEX PIndex ON Persons (LastName, FirstName)
DROP statement
Indexes, tables, and databases can easily be deleted/removed with the DROP statement.
ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
AND & OR operators
The AND & OR operators are used to filter records based on more than one condition.
BETWEEN Operator
The BETWEEN operator is used to select values within a range. 1) SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; 2) SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
CREATE TABLE statement
The CREATE TABLE statement is used to create a table in a database. CREATE TABLE Persons ( PersonID int, FirstName varchar(255), );
SQL DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. OrderDate date DEFAULT GETDATE()
FULL OUTER JOIN keyword
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
GROUP BY statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName;
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;
IN Operator
The IN operator allows you to specify multiple values in a WHERE clause. SELECT * FROM Customers WHERE City IN ('Paris','London');
Inner Join
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
INSERT INTO SELECT Statement
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. INSERT INTO Customers (CustomerName, Country) SELECT SupplierName, Country FROM Suppliers;
INSERT INTO statement
The INSERT INTO statement is used to insert new records in a table.
LEFT JOIN keyword
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
LIKE operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
ORDER BY keyword
The ORDER BY keyword is used to sort the result-set.
PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table Primary keys must contain UNIQUE values. CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
SELECT DISTINCT statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT INTO Statement
The SELECT INTO statement selects data from one table and inserts it into a new table. SELECT * INTO CustomersBackup2013 FROM Customers;
SELECT TOP Clause
The SELECT TOP clause is used to specify the number of records to return. SELECT TOP 2 * FROM Customers;
Select Statement
The SELECT statement is used to select data from a table.
UNION operator
The SQL UNION operator combines the result of two or more SELECT statements. Union All will select duplicate elements too.
UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table. P_Id int NOT NULL UNIQUE (Oracle, SQL Server) UNIQUE (P_Id) (MySQL) CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
UPDATE statement
The UPDATE statement is used to update records in a table. UPDATE table_name SET column1=value1,column2=value2, WHERE some_column=some_value;
WHERE clause
The WHERE clause is used to filter records.
Parameters for Protection SQL injection
The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters. parameters are represented in the SQL statement by a @ marker.
Difference between Truncate and Delete
Truncate will delete all records from table but keep the table, reset the counter. Delete can delete specified records but not reset the counter. Drop will delete table with structure of table.
Difference between Primary and Unique Key?
1) Each table can have only one primary key. Each table can have more than one Unique Constraint. 2) Primary key can be related with another table's as a Foreign Key. Unique Constraint can not be related with another table's as a Foreign Key. 3) Primary key supports Auto Increment value. Unique Constraint doesn't supports Auto Increment value.
SQL Wildcards
A wildcard character can be used to substitute for any other character(s) in a string. 1) SELECT * FROM Customers WHERE City LIKE 'ber%'; 2) SELECT * FROM Customers WHERE City LIKE '_erlin';
RDBMS
RDBMS stands for Relational Database Management Systems. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables.
What is SQL?
SQL (Structured Query Language) is used to perform operations on the records stored in database such as updating records, deleting records, creating and modifying tables, views etc.
SQL injection
SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. Injected SQL commands can alter SQL statement and compromise the security of a web application.
NULL Functions
SQLServer: SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products Oracle: SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products
