SQL

If you don't specify ASC or DESC after a SQL ORDER BY clause, the following is used by default:

ASC

What SQL clause is used to Delete data from a database table?

DELETE

What command is used to delete data from a SQL database table?

DELETE

What SQL keyword is used to eliminate duplicate fields from the SELECT output?

DISTINCT

What syntax would remove a database table?

DROP TABLE table;

DBMS stands for

Database Management System

What does follow after the SQL WHERE clause

Definition of the condition to be met for the rows to be returned.

What will be the result of the following SQL statement; SELECT LEFT ('Have a nice day.;4)

Have

The SQL BETWEEN operator

Specifies a range to test.

SQL Aggregate Functions

MIN, MAX, SUM, AVG, COUNT

When inserting data in a table do you always have to specify a list of all column names you are inserting values for?

No

What would add a new DATABASE named db?

CREATE DATABASE db

The table rows are also known as

Fields

WHERE CustomerName LIKE '%a'

Finds any values that end with 'a'

WHERE CustomerName LIKE '%or%'

Finds any values that have "or" in any position

WHERE CustomerName LIKE '_r%'

Finds any values that have "r" in the second position

WHERE CustomerName LIKE 'a%'

Finds any values that start with "a"

WHERE CustomerName LIKE 'a__%'

Finds any values that start with "a" and are at least 3 characters in length

WHERE ContactName LIKE 'a%o'

Finds any values that start with "a" and ends with "o"

What is the ABS SQL function used for

To return the absolute positive value of a numeric expression.

What is the syntax to change data in a table?

UPDATE

Wild Cards

is used to substitute one or more characters in a string, used with SQL LIKE operator.

What is the operator for the NOT equal to search condition?

<>

What is correct when we want to specify greater or equal search condition?

>=

What SQL functions is used to count the number of rows in SQL query?

COUNT()

What is a trigger?

A trigger is executed when certain event occurs.

What is view?

A view is a virtual table

The IN SQL keyword

Determines if a value matches any of the values in a list or sub-query.

UNION ALL =

Relational Addition

RDBMS stands for

Relational Database Management System

WHERE NOT EXISTS =

Relational Division

WHERE IN =

Relational Intersection

Select ______ FROM ______ , ________;

Relational Multiply

WHERE NOT EXISTS =

Relational Subtraction

UNION =

Relational Union

Normalization is

Removing ambiguity and redundancy

What is the MYSQL privilege used by the GRANT command to give a user READ-ONLY rights?

SELECT

What SQL statement selects the string 'Success'?

SELECT 'Success'

What SQL statement selects all rows from table called Contest, with column ContestDate having values greater or equal to May 25, 2006?

SELECT * FROM Contest WHERE ContestDate >= '05/25/2006'

What statement gets the total value of the column 'Price' in the 'Sales' table?

SELECT SUM(Price)FROM Sales

Change the order to field 4 ascending then field 2 descending

SELECT au_fname, au_lname, city, state FROM authors ORDER BY 4 ASC, 2 DESC;

Change the order multiple keys

SELECT au_fname, au_lname, city, state FROM authors ORDER BY state ASC, city DESC;

SQL syntax order

SELECT fields FROM tables WHERE selection of records or Boolean GROUP BY fields (subtotal) HAVING selection of aggregates ORDER BY fields (sorting)

Using a date

SELECT title_name, pubdate FROM titles WHERE pubdate >= DATE '2001-01-01';

Multiple math selection

SELECT title_name, type, price FROM titles WHERE type = 'biography' AND price < 20;

What does the following SQL statement do? SELECT Customer,COUNT(Order)FROM Sales GROUP BY Customer HAVING COUNT(Order)>5;

Selects all customers from table Sales that have made more than 5 orders

What is the difference between the DELETE and TRUNCATE SQL clause?

The DELETE clause deletes all rows in a database table, while the TRUNCATE clause can have WHERE condition and might or might not delete all rows in a table.

What does the FROM SQL keyword specify?

The FROM SQL keyword specifies the tables

What does the Having clause do?

The HAVING keyword specifies a search condition for an aggregate

What does the UNION ALL operator do?

The UNION ALL operator combines the result of two or more queries into a one result that includes all the rows from the queries in the union with duplicates.

What is the difference between the WHERE and HAVING SQL clauses?

The WHERE clause condition is applied to all rows in the result set before the Having clause is applied. The Having clause is used only with SELECT statement and specifies a search condition for an aggregate or a group.

What will be the result of the following SQL statement: SELECT * FROM Table1 where Column1 > 10?

The result will be all rows from Table1 which have Column1 values greater than 10.

What is the ABS SQL function used for?

To return the average value of a numeric expression

What command should one use to change the value of rating to 'PG' for movie_id 23 in table movies?

UPDATE movies SET rating="PG" WHERE movie_id =23;

There are triggers for

Update, Delete and Insert

In SQL the third order clause is

WHERE

What SQL keyword is used to specify conditional search?

WHERE

The LIKE SQL keyword is used along with

WHERE clause

Can you use both SELECT and WHERE in a SQL clause in one statement

YES

DELETE FROM table WHERE column=value; Would this statement execute without error?

YES

If A=[1,2,3] and B=[2,3,4] What would A add B be?

[1,2,2,3,3,4]

If A=[1,2,3] and B =[2,3,4] What would A union B be?

[1,2,3,4]

If A=[1,2,3] and B = [2,3,4] What would A subtract B be?

[1]

The AVG SQL function returns the

average in the values in a group.

CREATE TABLE statement is used to

create a new table

The TRUNCATE TABLE

deletes all rows from a table

NATURAL JOIN =

equal

CASE statement is?

goes though conditions and returns a value when the first condition is met. Once true it will return result, if false returns value in the ELSE clause.