SQL Essential Training

Ace your homework & exams now with Quizwiz!

Which statement is true about selecting columns in SQL?

Column names must be separated by commas.

How do you add a new column and its data type to an existing table?

Once a table has been created, use the ALTER TABLE statement to add a new column and its data type.

What does CRUD stand for?

Create Read Update Delete

To insert a blank row use the _____ clause.

DEFAULT VALUES

What clause is used to insert a blank row?

DEFAULT VALUES

The _____ statement is used to remove rows from a table.

DELETE

The___statement is used to remove rows from a table

DELETE

What is the correct syntax for deleting only rows in a table named Vehicles where the Name equals "Ford"?

DELETE FROM Vehicles WHERE Name='Ford';

Which code deletes row 10 from the table sample?

DELETE FROM sample WHERE id=10;

What is the correct code to remove row 3 from the student table?

DELETE FROM student WHERE id=3;

Which SQL keyword is used to list data in largest to smallest order?

DESC

The SELECT _____ statement removes duplicates from a result set.

DISTINCT

Use the ___ keyword to remove duplicates from a result.

DISTINCT

To delete a table, use the _____ statement.

DROP TABLE

What statement is used to delete a table?

DROP TABLE

Which code will delete the table, cars, from a database?

DROP TABLE cars;

Clauses

FROM, WHERE, etc.

T/F: The NULL value is equivalent to a zero.

False

T/F: The SUBSTR() function is standard SQL

False

T/F: a subselect is performed with the SUBSELECT statement

False

T/F: numeric types are standardized across platforms.

False

T/F: the NULL value is equivalent to a zero

False

T/F: the SQL standard provides standardized date and time functions

False

What are databases for?

Organizing and making data available in different forms

Which SQL function is used to round a numeric number?

ROUND ()

Which statement is true about removing rows from a table?

Remove existing rows from a table with the DELETE FROM statement.

What is the purpose of a foreign key?

Foreign keys refer to the primary key in another table to link the two tables together.

Use the ___ clause to collate groups of results before calling an aggregate function

GROUP BY

Which statement is not an SQL keyword used in the CASE statement?

IF

The ___ statement is used to add a row to a table

INSERT

The _____ statement is used to add a row to a table.

INSERT

How can a row of data be inserted into a table named Animals?

INSERT INTO Animals (Name, Age, Type) VALUES ('Fido', 10, 'Dog');

What is the correct code to add a row with values for name, systolic, and diastolic to the patient table?

INSERT INTO patient (name, systolic, diastolic) values ('Lily Parker', 115, 72);

You can undo an unfinished transaction by using the ___ statement.

Rollback

A LEFT JOIN includes rows that match the JOIN criteria as well as non-matching rows from the _____ table.

LEFT

Which SQL statement allows you to retrieve data from a SQL database?

SELECT

Which Boolean operator would filter a list of names to match only those containing "r" as the third letter?

Names LIKE '__r%'

Which Boolean operator would filter a list of names to match only those containing 'r' as the third letter?

Names LIKE '__r%'

What is an example of a query to get only data containing the word "Apple"?

SELECT * FROM Fruits WHERE Name = 'Apple';

what is an example of a query to get only data containing the word "Apple"?

SELECT * FROM Fruits WHERE Name = 'Apple';

Which code returns the first 10 students who have a 4.0 GPA from the Grade table ordered alphabetically?

SELECT * FROM Grade WHERE GPA=4.0 ORDER BY Name LIMIT 10;

How do you return the rows with null values in the age column in the patient table?

SELECT * FROM patient WHERE age IS NULL;

How could you quickly calculate the average value of the data in a column called "kernels" in a table called "Corn"?

SELECT AVG(kernels) FROM Corn;

What is the best way to return the number of rows in the student table?

SELECT COUNT (*) FROM student;

Which code returns the CustomerName column in descending order in the eshop table?

SELECT CustomerName from eshop ORDER BY CustomerName DESC;

What is an example of SQL syntax to add one day to the current day?

SELECT DATETIME ('now', '+1 day');

Which statement removes duplicates from a result set?

SELECT DISTINCT

How do you select the unique row values from the columns CustomerName and ShopLocation in the eshop table?

SELECT DISTINCT CustomerName, ShopLocation FROM eshop;

What is the proper syntax for aliasing a field?

SELECT FirstName AS "First Name";

Which code displays all of the students taking the AP course 'APcalculus'?

SELECT FirstName, LastName FROM students WHERE APcourse = 'APcalculus';

What is the proper syntax to remove spaces from both the start and end of a field?

SELECT TRIM (' Hello ');

How can you determine the type of a value in SQL?

SELECT TYPEOF ()

Which code returns the columns midtermexam, finalexam, and gpa from the grade table?

SELECT midtermexam, finalexam, gpa FROM Grade;

What is the result of an inner join?

The results will include the rows from both tables where the join condition is met.

Which clauses allow you to get rows and columns from a table in the database?

The select statement retrieves data from the database by easily selecting the rows and columns from a table.

What is the result of the following statement: SELECT TRIM ('The test was a treat', 't');

The test was a trea

Why can the use of real data types cause difficulties?

They sacrifice precision for scale

What is the result of the following code? CREATE TABLE patient ( name TEXT, age INTEGER, height INTEGER, weight INTEGER, systolic INTEGER, diastolic INTEGER );

This code creates a table with six columns and no row entries.

Why is it important to convert all fields to the same case?

SQLite compares data based on case

Why do you need to be careful and consult documentation when altering an existing table?

Syntax capabilities vary widely from one implementation of SQL to another

A LEFT JOIN includes rows that match the JOIN criteria as well as non-matching rows from which table?

The LEFT table

What are the three arguments for the SUBSTR() function in SQLite?

(string to evaluate, starting position, length of string)

In standard SQL, which value is interpreted as false?

0

If you use UPDATE and SET without a WHERE clause, what happens?

The entire table is updated

What is the result of SELECT 30/7, 30 % 7?

4 | 2

What is the difference between a WHERE clause and a HAVING clause?

A HAVING clause operates on aggregated data A WHERE clause operates on unaggregated data

Foreign Keys

A column or group of columns used to represent relationships. Values of the foreign key match values of the primary key in a different (foreign) table.

What does the WHERE clause requirement?

A logical expression ex: WHERE Countries = 'Europe;

Why is a junction table necessary when combining data between tables?

A relationship is provided between the tables so they can be linked

An SQL statement is terminated with___

A semicolon

Which statement is true about the id column in a SQL table?

A table can only have one id column.

The SELECT statement is used for___

All queries

How many tables can a joined query include?

Any number of tables

The type of trigger to use for preventing updates to reconciled rows is...

BEFORE UPDATE

What is the SQL command used to make a new table?

CREATE TABLE <table_name>

How can a constraint be placed on a table where a field will contain the value "Mickey" if nothing is provided?

CREATE TABLE People (FirstName TEXT DEFAULT 'Mickey', address TEXT, city, TEXT);

How do you create a table and insert rows?

CREATE TABLE test ( a INTEGER, b TEXT ); INSERT INTO test VALUES ( 1, 'two' ); SELECT * FROM test;

What is the correct format for creating an ID column for the Vehicles table in SQLite?

CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);

What is the correct format for creating an ID column for the Vehicles table?

CREATE TABLE vehicles (VehicleID INTEGER PRIMARY KEY);

To save a query as a view, use the ___ statement

CREATE VIEW

Why would you use a trigger?

To automatically execute a group of statements whenever a specified event occurs

Why is aggregate data used?

To derive information from more than one row at a time

Why would you use column constrainsts?

To make sure that values obey desired rules or behaviors

T/F: A JOINed query can be used as a data source in a subselect

True

T/F: A joined query operates on columns from two or more tables in one query.

True

T/F: Column constraints are applied when the table is created.

True

T/F: Every database management system uses its own syntax for ID columns.

True

T/F: SQL Dates and times are express in the UTC time zone

True

T/F: The ORDER BY clause can use multiple columns for ordering criteria.

True

T/F: The WHERE clause is used with UPDATE to determine which rows are updated.

True

T/F: You can query columns in any order.

True

T/F: You can use the COUNT(*) function to count all the rows in a table.

True

T/F: a VIEW is like a stored subselect

True

T/F: a joined query operates on columns from two or more tables in one query.

True

T/F: a transaction can drastically improve the performance of a set of write operations

True

T/F: aggregate functions operate on groups of rows

True

T/F: column constraints are applied when the table is created

True

T/F: every database management system uses its own syntax for ID columns.

True

T/F: the ORDER BY clause can use multiple columns for ordering criteria.

True

T/F: the UPPER() and LOWER() functions can be used to fold the case of a string for sorting and comparisons.

True

T/F: there WHERE clause is used with UPDATE to determine which rows are updated

True

T/F: transactions ensure that a number of statements are performed as a unit

True

T/F: triggers are widely implemented, but they're often implemented differently

True

T/F: you can query columns in any order

True

T/F: you can use the COUNT(*) function to count all the rows in a table

True

Which constraint ensures a column cannot have any duplicate values including null values?

UNIQUE NOT NULL

Which code is the correct way to update the GPA for the student in row 7 from the student table?

UPDATE student SET GPA=3.5 WHERE id=7;

What clause correctly tests for a NULL value in a column?

WHERE a IS NULL

Which clause correctly tests for a NULL value in column a?

WHERE a IS NULL

When should you create a view from a SELECT statement?

When you want to modify the existing displayed data

Which statement is true about inserting data to a table?

You must execute the select statement after the insert into statement to see the result with the added row.

An SQL statement is terminated with _____.

a semicolon

A joined query can include _____ tables.

any number of

How do you specify the columns to be selected?

by name or column heading

In the statement SELECT * FROM Customers WHERE Age > 50;, what is the formal name for FROM Customers?

clause

In the statement: SELECT * FROM Customers WHERE Age > 50; what is the formal name for "FROM Customers"?

clause, it modifies the verb 'SELECT'

How do you execute a query?

click F9

The CREATE TABLE statement _____.

defines the table columns defines the table schema creates a table

What do you call reverse alphabetical order in SQL jargon?

descending

What do you call reverse alphabetical order in SQL?

descending

T/F: the LENGTH() function is standard in SQL

false

T/F: the epxression 17/5 will return the real value 3.4

false

The WHERE clause is used to _____.

filter query results by row

Use the TYPEOF() function to...

find the type of an expression

What type of relationship requires the use of a junction table?

many-to-many

The essence of subselection is___

nested select statements, the output from one selection is used as the input to another

The ROUND() Function will...

round a numeric value to a number of decimal places

Standard SQL uses what to delimit a literal string?

single quotes ' '

What clause is used to delete a row?

the WHERE clause with the DELETE statement

To what does a table schema refer?

the number of, titles for, and data types in columns

What is a PRIMARY KEY?

the unique key for a table when exposed as a column

Statement

the unit of execution ex: SELECT ** FROM Countries WHERE Continent = 'Europe';

How do transactions improve performance?

they speed up execution by making reading and writing more efficient

What is the purpose of the JOIN clause?

to combine results from related rows in multiple tables

In the statement COUNT(*), what does the * mean?

to consider all rows, it's often used as a wildcard

What is the WHERE clause used for?

to filter query results by row

Why would you use transactions instead of individual instructions?

to maintain data integrity and improve performance

Why would you use column constraints?

to make sure that values obey desired rules or behaviors

What is the function of an AS clause?

to name a column

What is the function of the AS clause?

to name a column

Why would you use the SUBST() function with only two arguments?

to retrieve a substring from a specified starting point to the end of the string

T/F: SQL provides a conditional expression that allows different results based on a logical value.

true

Two tables are defined as up: id tt 0 t0 1 t1 and down: id tb 1 b1 2 b2 What is the result of the following query: SELECT up.tt, down.tb FROM up JOIN down ON up.id=down.id

tt tb t1 b1

How can the length of a field be found using SQLite

use the LENGTH() function

What is the standard way to concatenate two strings?

using the concatenation operator ||

Which format conforms to the SQL standard for datetime?

year-month-day hour:minute:second

The type of trigger to use for adding timestamps is...

AFTER INSERT

The _____ is used to change a table schema.

ALTER

What is used to change a table schema?

ALTER

Which statement is true about the DROP TABLE statement?

The DROP TABLE statement enables you to delete a table from a database and if the table did not exist then attempting to DROP it would cause an error to occur.

The ___ clause is used with SELECT to filter rows.

WHERE

The _____ clause is used with SELECT to filter rows.

WHERE

To delete a row use the DELETE statement with the _____ clause.

WHERE

The SELECT statement is used for _____.

all queries


Related study sets

CRPC | Designing Optimal Retirement Income Streams

View Set

Torts MC Midterm (Negligence) Practice Questions

View Set

Beginning and End of Week 1 quiz

View Set

Chapter 25: The Fetal Face & Neck

View Set

Renewable and Non Renewable Energy

View Set

4. Prepositions 전치사 - on 착! 달라붙어 있는 ~(위)에

View Set