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.
