Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL 101 > SQL Need to Know > Flashcards

SQL Need to Know Flashcards

You may prefer our related Brainscape-certified flashcards:
Chemistry 101 flashcards Psychology 101 flashcards Sociology 101 flashcards Organic Chemistry 101 flashcards Physics 101 flashcards Biology 101 flashcards
1
Q

SQL CREATE DATABASE

A

CREATE DATABASE databasename;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

SQL DROP DATABASE

A

DROP DATABASE databasename;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SQL BACKUP DATABASE

A

BACKUP DATABASE databasename
TO DISK = ‘filepath’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

SQL CREATE TABLE

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

SQL Create Table Using Another Table

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SQL DROP TABLE

A

DROP TABLE table_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SQL TRUNCATE TABLE

A

TRUNCATE TABLE table_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

SQL ALTER TABLE - ADD Column

A

ALTER TABLE table_name
ADD column_name datatype;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

ALTER TABLE - DROP COLUMN

A

ALTER TABLE table_name
DROP COLUMN column_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

ALTER TABLE - RENAME COLUMN

A

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

ALTER TABLE - ALTER/MODIFY DATATYPE

A

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

SQL Create Constraints

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

SQL NOT NULL on CREATE TABLE

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

SQL NOT NULL on ALTER TABLE

A

ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

SQL UNIQUE Constraint on CREATE TABLE

A

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)
);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

SQL UNIQUE Constraint on ALTER TABLE

A

ALTER TABLE Persons
ADD UNIQUE (ID);

multiple columns:

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

DROP a UNIQUE Constraint

A

ALTER TABLE Persons
DROP INDEX UC_Person;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

SQL PRIMARY KEY

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

SQL PRIMARY KEY on CREATE TABLE

A

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)
);

21
Q

SQL PRIMARY KEY on ALTER TABLE

A

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

multiple columns:

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

22
Q

DROP a PRIMARY KEY Constraint

A

ALTER TABLE Persons
DROP PRIMARY KEY;

23
Q

SQL PRIMARY KEY Constraint Definition

A

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

24
Q

SQL UNIQUE Constraint Definition

A

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.

25
Q

SQL NOT NULL Constraint Definition

A

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.

26
Q

SQL FOREIGN KEY Constraint Definition

A

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.

27
Q

SQL CHECK Constraint Definition

A

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.

28
Q

SQL DEFAULT Constraint Definition

A

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.

29
Q

SQL FOREIGN KEY on CREATE TABLE

A

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)
);

30
Q

SQL FOREIGN KEY on ALTER TABLE

A

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);

31
Q

DROP a FOREIGN KEY Constraint

A

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

32
Q

SQL CHECK on CREATE TABLE

A

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’)
);

33
Q

SQL CHECK on ALTER TABLE

A

ALTER TABLE Persons
ADD CHECK (Age>=18);

multiple columns:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=’Sandnes’);

34
Q

DROP a CHECK Constraint

A

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

35
Q

SQL DEFAULT on CREATE TABLE

A

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()
);

36
Q

SQL DEFAULT on ALTER TABLE

A

SQL DEFAULT on ALTER TABLE

37
Q

DROP a DEFAULT Constraint

A

ALTER TABLE Persons
ALTER City DROP DEFAULT;

38
Q

CREATE INDEX Syntax

A

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

39
Q

CREATE UNIQUE INDEX Syntax

A

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

40
Q

DROP INDEX Statement

A

ALTER TABLE table_name
DROP INDEX index_name;

41
Q

AUTO INCREMENT Syntax for MySQL

A

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.

42
Q

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

A

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’);

43
Q

SQL Date Data Types (4)

A

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

44
Q

SQL CREATE VIEW Syntax

A

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

45
Q

SQL Updating a View

A

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

46
Q

SQL DROP VIEW Syntax

A

DROP VIEW view_name;

47
Q

What is SQL Injection?

A

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.

48
Q
A

SQL 101 (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions