ragabmohamedragab / sql-interview Goto Github PK

A view is a virtual table that triggers from join between two tables or more .

Create :

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Update:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Drop :

DROP VIEW view_name;

Temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete), join, and some other operations like the persistent database tables. Temporary tables are dropped when the session that creates the table has closed

• Local Temporary Table
• Global Temporary Table

Local Temporary

The local temporary tables are visible for the duration of the connection and when the session is closed the local temporary table are dropped automatically. Local temporary tables can be created using the “CREATE TABLE” syntax but a single hashtag (#) sign must be added in front of their names

Create

CREATE TABLE #TempPersonTable (
    PersonID int PRIMARY KEY IDENTITY(1,1),
    LastName varchar(255),
    FirstName varchar(255),
    City varchar(255)
)

After creating the table, we can insert data into it as the persisted tables.

INSERT INTO #TempPersonTable
VALUES
( 'Watson', 'Juan', 'Cleveland'),
( 'Baker', 'Dwayne', 'Fort Wayne'),
( 'Walker', 'Eric', 'Tucson'),
( 'Peterson', 'Bob', 'Indianapolis');
 
SELECT *
FROM #TempPersonTable;

Global Temporary Table

These tables can be accessed by all other sessions. he CREATE TABLE statement and their names must be prefixed with the double hashtag (##) sign.

CREATE TABLE ##Customers (CustomerId INT IDENTITY(1,1) PRIMARY KEY,
CustomerFullName VARCHAR(50),
EMail VARCHAR(50),
CustomerAddress VARCHAR(50),
Country VARCHAR(50))

After creating the temporary table, we can populate and query it.

INSERT INTO ##Customers
VALUES('Olivia Rodriguez','[email protected]','66 Elizabeth Street','Canada'),
('Riley Hall','[email protected]','23 Brookside Drive','U.K.') ,
('Allison Lopez','[email protected]','11 Church Road','South Africa'),
('Gianna King','[email protected]','193 Park Place','Belgium')
 
SELECT * FROM ##Customers

Delete Temp Table

BEGIN TRAN 
DELETE FROM #TestTempTable

BEGIN TRAN 
DELETE FROM ##TestTempTable

Primary Key is one of constraint Not Accepted Null and Duplicated data in the column.

It can be one or more key in the table.

uniquely identifies each record in a table Create

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Drop

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

Default is a contraint , Set Default value to column.

Create

CREATE TABLE Orders (
    ID int NOT NULL PRIMARY KEY ,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables(Transver Dependency).

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table

Create

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

Alter

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Drop

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

TRUNCATE is Data definition language (DDL) , Used to remove all the records in the table.

TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

Delete is Data manipulation language (DML) , Used to delete specify record in the table based on where clause.

DELETE FROM table_name WHERE condition;

It is database design used to reduce data redundenty Easier To insertion and updating and deleting. It is Splited large table into Two tables and make relationships between two table.

• First Normal Form (1NF)
  1. Removing Repeat group (Multi - values)
  2. Removing Repeat Similar Name Columns
  3. Data of cell must be saming Data types of the column

Example

 Second Normal Form (2NF)


  - 1NF
 -  Removing Partial Dependency (Must be Finding Composite Primary Key To achive Partial Dependency)

C_Id dependence on course_name , here we must remove this partial dependency.
 
 - First Table
 
 ------------------------
 |   Std_Id  |  grade |
 ------------------------
 |   1       |     A   |
 -----------------------
 |   2       |    B    | 
 -----------------------
- Second Table
 ------------------------
 |   C_Id | course_name |
 ------------------------
 |    2   |    C#       |
 -----------------------
 |    3   |    C++      | 
 -----------------------

2NF

Removing transitive dependency ,SO must split table into two tables.

DDL

   Alter - Truncate - Drop - Create

DML

 Update - Delete - Insert 
 

Generated A new Table from join between two tables or more.

Inner join : retuen only Matches rows between tables.

SELECT ID , Name ,DepartMentName From 
  DepartMentL1 INNER JOIN EmployeeR2 
  ON DepartMentL1.ID=EmployeeR2.DeptID;

Left Join : return Matches Rows between tables and not matches in Left Table

 SELECT ID , Name ,DepartMentName From 
  DepartMentL1 LEFT/LEFT OUTER JOIN EmployeeR2 
  ON DepartMentL1.ID=EmployeeR2.DeptID;


Right Join : return Matches rows between tables and not matches in right tables.


 SELECT ID , Name ,DepartMentName From 
  DepartMentL1 Right/Right OUTER JOIN EmployeeR2 
  ON DepartMentL1.ID=EmployeeR2.DeptID;



 Index

It Concept in Sql server that we can Create Index in each Column for table to speed retrive data from table.

```
CREATE INDEX IX_tbelEmployee_Salary 
ON tabelEmployee(Salary ASC)
```