SQL Need to Know

Question 1

SQL CREATE DATABASE

Answer 1

CREATE DATABASE databasename;

Question 2

SQL DROP DATABASE

Answer 2

DROP DATABASE databasename;

Question 3

SQL BACKUP DATABASE

Answer 3

BACKUP DATABASE databasename
TO DISK = ‘filepath’;

Question 4

SQL BACKUP WITH DIFFERENTIAL

A differential back up only backs up the parts of the database that have changed since the last full database backup.

Answer 4

BACKUP DATABASE databasename
TO DISK = ‘filepath’
WITH DIFFERENTIAL;

Question 5

SQL CREATE TABLE

Answer 5

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);

Question 6

SQL Create Table Using Another Table

Answer 6

CREATE TABLE new_table_name AS
SELECT column1, column2,…
FROM existing_table_name
(OPTIONAL) WHERE ….;

Question 7

SQL DROP TABLE

Answer 7

DROP TABLE table_name;

Question 8

SQL TRUNCATE TABLE

Answer 8

TRUNCATE TABLE table_name;

Question 9

SQL ALTER TABLE - ADD Column

Answer 9

ALTER TABLE table_name
ADD column_name datatype;

Question 10

ALTER TABLE - DROP COLUMN

Answer 10

ALTER TABLE table_name
DROP COLUMN column_name;

Question 11

ALTER TABLE - RENAME COLUMN

Answer 11

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Question 12

ALTER TABLE - ALTER/MODIFY DATATYPE

Answer 12

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Question 13

SQL Create Constraints

Answer 13

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
….
);

Question 14

SQL NOT NULL on CREATE TABLE

Answer 14

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

Question 15

SQL NOT NULL on ALTER TABLE

Answer 15

ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;

Question 16

SQL UNIQUE Constraint on CREATE TABLE

Answer 16

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

multiple columns:

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

Question 17

SQL UNIQUE Constraint on ALTER TABLE

Answer 17

ALTER TABLE Persons
ADD UNIQUE (ID);

multiple columns:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

Question 18

DROP a UNIQUE Constraint

Answer 18

ALTER TABLE Persons
DROP INDEX UC_Person;

Question 19

SQL PRIMARY KEY

Answer 19

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

Question 20

SQL PRIMARY KEY on CREATE TABLE

Answer 20

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

more columns:

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

Question 21

SQL PRIMARY KEY on ALTER TABLE

Answer 21

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

multiple columns:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Question 22

DROP a PRIMARY KEY Constraint

Answer 22

ALTER TABLE Persons
DROP PRIMARY KEY;

Question 23

SQL PRIMARY KEY Constraint Definition

Answer 23

The 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; and in the table, this primary key can consist of single or multiple columns (fields).

Question 24

SQL UNIQUE Constraint Definition

Answer 24

The 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.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Question 25

SQL NOT NULL Constraint Definition

Answer 25

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Question 26

SQL FOREIGN KEY Constraint Definition

Answer 26

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Question 27

SQL CHECK Constraint Definition

Answer 27

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 column it will allow 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.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 column it will allow 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.

Question 28

SQL DEFAULT Constraint Definition

Answer 28

The 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.

Question 29

SQL FOREIGN KEY on CREATE TABLE

Answer 29

CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); multiple columns: 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) );

Question 30

SQL FOREIGN KEY on ALTER TABLE

Answer 30

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

Question 31

DROP a FOREIGN KEY Constraint

Answer 31

ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;

Question 32

SQL CHECK on CREATE TABLE

Answer 32

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) ); multiple columns: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );

Question 33

SQL CHECK on ALTER TABLE

Answer 33

ALTER TABLE Persons ADD CHECK (Age>=18); multiple columns: ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

Question 34

DROP a CHECK Constraint

Answer 34

ALTER TABLE Persons DROP CHECK CHK_PersonAge;

Question 35

SQL DEFAULT on CREATE TABLE

Answer 35

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' ); The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE(): CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() );

Question 36

SQL DEFAULT on ALTER TABLE

Answer 36

SQL DEFAULT on ALTER TABLE

Question 37

DROP a DEFAULT Constraint

Answer 37

ALTER TABLE Persons ALTER City DROP DEFAULT;

Question 38

CREATE INDEX Syntax

Answer 38

CREATE INDEX index_name ON table_name (column1, column2, ...);

Question 39

CREATE UNIQUE INDEX Syntax

Answer 39

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Question 40

DROP INDEX Statement

Answer 40

ALTER TABLE table_name DROP INDEX index_name;

Question 41

AUTO INCREMENT Syntax for MySQL

Answer 41

CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) ); MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

Question 42

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

Answer 42

ALTER TABLE Persons AUTO_INCREMENT=100; To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically): INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen');

Question 43

SQL Date Data Types (4)

Answer 43

DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: YYYY-MM-DD HH:MI:SS YEAR - format YYYY or YY

Question 44

SQL CREATE VIEW Syntax

Answer 44

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

Question 45

SQL Updating a View

Answer 45

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

Question 46

SQL DROP VIEW Syntax

Answer 46

DROP VIEW view_name;

Question 47

What is SQL Injection?

Answer 47

SQL injection is a code injection technique that might destroy your database. SQL injection is one of the most common web hacking techniques. SQL injection is the placement of malicious code in SQL statements, via web page input.