Brainscape's Knowledge GenomeTM

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


See full index

QA Interview Q&A > Advance SQL Statements > Flashcards

Advance SQL Statements Flashcards

1
Q

What is the SELECT TOP clause used for?

A

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note: Not all database systems support the SELECT TOP clause.

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

What is the syntax for the SELECT TOP clause SQL Server / MS Access statement?

A

SELECT TOP number|percent column_name(s)

FROM table_name;

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

What is the syntax for the SELECT TOP clause MySQL statement?

A

SELECT column_name(s)
FROM table_name
LIMIT number;

Ex.
SELECT *
FROM Persons
LIMIT 5;

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

What is the syntax for the SELECT TOP clause Oracle statement?

A

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <=5;

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

What is the SELECT TOP clause statement to select the first top 2 records?

A

SELECT TOP 2 * FROM Customers;

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

What is the SELECT TOP clause statement to select percent?

A

SELECT TOP 50 PERCENT * FROM Customers;

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

What is the LIKE operator used for?

A

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

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

What is the syntax for the LIKE operator statement?

A

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Ex.
SELECT * FROM Customers
WHERE City LIKE ‘s%’;

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

What is the statement for the LIKE operator when letters are missing?

A

SELECT * FROM Customers
WHERE City LIKE ‘%s’;

This statement selects all customers with a City ending with the letter “s”:

SELECT * FROM Customers
WHERE Country LIKE ‘%land%’;

This statement selects all customers with a Country containing the pattern “land”:

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

What is the statement for the LIKE operator using the NOT keyword?

A

Ex.
SELECT * FROM Customers
WHERE Country NOT LIKE ‘%land%’;

Using the NOT keyword allows you to select records that does NOT match the pattern.

This statement selects all customers with a Country NOT containing the pattern “land”:

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

What is the purpose of the IN operator?

A

The IN operator allows you to specify multiple values in a WHERE clause.

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

What is the syntax for the IN operator statement?

A

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…);

Ex.
SELECT * FROM Customers
WHERE City IN (‘Paris’,’London’);

This statement selects all customers with a City of “Paris” or “London”:

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

What is the BETWEEN operator used for?

A

The BETWEEN operator is used to select values within a range.

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

What is the syntax for the BETWEEN operator statement?

A

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Ex.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

This statement selects all products with a price BETWEEN 10 and 20:

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

What is the statement for the NOT BETWEEN operator?

A

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

This statement displays the products outside the range of the previous example, use NOT BETWEEN:

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

What is the statement for the BETWEEN operator with IN?

A

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

This statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

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

What is the statement for the BETWEEN operator with text value?

A

SELECT * FROM Products
WHERE ProductName BETWEEN ‘C’ AND ‘M’;

This statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’:

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

What is the statement for the NOT BETWEEN operator with text value?

A

SELECT * FROM Products
WHERE ProductName NOT BETWEEN ‘C’ AND ‘M’;

This statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN ‘C’ and ‘M’:

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

What is the statement for the BETWEEN operator with date value?

A

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

This statement selects all orders with an OrderDate BETWEEN ‘04-July-1996’ and ‘09-July-1996’:

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

What are JOIN used for?

A

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

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

What is the most common type of JOIN?

A

SQL INNER JOIN (simple join). It return all rows from multiple tables as long as there is a match between the columns in both tables.

22
Q

What is the syntax for the INNER JOIN keyword statement?

A

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

Ex.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

This statement will return all customers with orders:

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the “Customers” table that do not have matches in “Orders”, these customers will NOT be listed.

PS! INNER JOIN is the same as JOIN.

23
Q

What is the purpose of the LEFT JOIN?

A

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

24
Q

What is the syntax for the LEFT JOIN keyword statement?

A

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Ex.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

This statement will return all customers, and any orders they might have:

Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

25
Q

What is the purpose of the RIGHT JOIN?

A

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

26
Q

What is the syntax for the RIGHT JOIN keyword statement?

A

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Ex.
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

This statement will return all employees, and any orders they have placed:

Note: The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).

PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

27
Q

What does the FULL OUTER JOIN do?

A

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). It combines the result of both LEFT and RIGHT joins.

28
Q

What is the syntax for the FULL OUTER JOIN keyword statement?

A

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Ex.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

This statement selects all customers, and all orders:

Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.

29
Q

What is a PRIMARY KEY & what does it do?

A

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

30
Q

What is the PRIMARY KEY statement for MySQL to create “Person” table?

A 
CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 PRIMARY KEY (P_Id)
 )
31
Q

What is the PRIMARY KEY statement for SQL Server / Oracle / MS Access to create “Person” table?

A 
CREATE TABLE Persons
 (
 P_Id int NOT NULL PRIMARY KEY,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 )
32
Q

What is the SQL syntax to allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns?

A 
CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
 )

Same for MySQL / SQL Server / Oracle / MS Access

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).

33
Q

What is the SQL syntax to create a PRIMARY KEY constraint on the “P_Id” column when the table is already created?

A

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

Same for MySQL / SQL Server / Oracle / MS Access

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

34
Q

What is the SQL syntax to allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, to alter it?

A

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Same for MySQL / SQL Server / Oracle / MS Access

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

35
Q

What is the SQL syntax is used to drop a PRIMARY KEY constraint in MySQL?

A

ALTER TABLE Persons

DROP PRIMARY KEY

36
Q

What is the SQL syntax is used to drop a PRIMARY KEY constraint in SQL Server / Oracle / MS Access?

A

ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID

37
Q

What is a FOREIGN KEY & what does it do?

A

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

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

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

38
Q

What is the FOREIGN KEY statement for MySQL to create “Order” table?

A 
CREATE TABLE Orders
 (
 O_Id int NOT NULL,
 OrderNo int NOT NULL,
 P_Id int,
 PRIMARY KEY (O_Id),
 FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
 )
39
Q

What is the FOREIGN KEY statement for SQL Server / Oracle / MS Access to create “Order” table?

A 
CREATE TABLE Orders
 (
 O_Id int NOT NULL PRIMARY KEY,
 OrderNo int NOT NULL,
 P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
 )
40
Q

What is the SQL syntax to allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns?

A 
CREATE TABLE Orders
 (
 O_Id int NOT NULL,
 OrderNo int NOT NULL,
 P_Id int,
 PRIMARY KEY (O_Id),
 CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
 REFERENCES Persons(P_Id)
 )

Same for MySQL / SQL Server / Oracle / MS Access

41
Q

What is the SQL syntax to create a FOREIGN KEY constraint on the “P_Id” column when the table is already created?

A

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Same for MySQL / SQL Server / Oracle / MS Access

42
Q

What is the SQL syntax to allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, to alter it?

A

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Same for MySQL / SQL Server / Oracle / MS Access

43
Q

What is the SQL syntax is used to drop a FOREIGN KEY constraint in MySQL?

A

ALTER TABLE Orders

DROP FOREIGN KEY fk_PerOrders

44
Q

What is the SQL syntax is used to drop a FOREIGN KEY constraint in SQL Server / Oracle / MS Access?

A

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

45
Q

What is the most difficult part with working with SQL dates?

A

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.

46
Q

What are the built-in date functions for MySQL?

A

NOW() Returns the current date and
time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a
date or date/time expression
EXTRACT() Returns a single part of a
date/time
DATE_ADD() Adds a specified time interval
to a date
DATE_SUB() Subtracts a specified time
interval from a date
DATEDIFF() Returns the number of days
between two dates
DATE_FORMAT() Displays date/time data in
different formats

47
Q

What are the built-in date functions for SQL Server?

A

GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time
interval from a date
DATEDIFF() Returns the time between two
dates
CONVERT() Displays date/time data in different
formats

48
Q

What are the data types for storing a date or a date/time value in the MySQL database?

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

Note: The date types are chosen for a column when you create a new table in your database!

49
Q

What are the data types for storing a date or a date/time value in the SQL Server database?

A 
• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - format: YYYY-MM-DD
   HH:MM:SS
• TIMESTAMP - format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

50
Q

What is a example of a Date statement?

A

SELECT * FROM Orders WHERE OrderDate=’2008-11-11’

51
Q

How can you keep your queries simple and easy to maintain?

A

Don’t allow time components in your dates, because two dates can easily be compared if there is no time component involved!

QA Interview Q&A (6 decks)

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