COB 204 Databases

What is SQL?

Structured Query Language

define reports

Structured presentation of data using sorting, grouping, filtering, other operations

little database equation

Tables or Files + Relationships Among Rows in Tables + Metadata = Database

what are the field and table names in this statement? what do they do? SELECT first_name, last_name FROM quiz_results WHERE state_code = 'CA' ORDER BY last_name ASC;

- Field and table names specify where to look and what to look for Field: first_name, last_name state_code last_name Table: quiz_results

What does SQL do?

- Lets us formulate questions a database can respond to - Gives us a way of writing questions a database or computer can understand

What is the purpose of a database? what is the general rule of themes? what is a theme?

- Organize and keep track of things - Keep track of multiple themes General Rule: - Single theme - store in a spreadsheet - Multiple themes - use a database - Ex: student grades, student emails, student office visits

what are the predicates in this statement? what do they do? SELECT first_name, last_name FROM quiz_results WHERE state_code = 'CA' ORDER BY last_name ASC;

- Predicates are conditions, and expressions represent values state_code = 'CA' -- 'CA' is the expression

what does Database administration do?

- Set up security system, user accounts, passwords, permissions, limits for processing - Limit user permissions - Backup database, improve performance of database application, remove unwanted data

What do databases do?

- Show relationship between tables - File - Search, Sort (reports), modify (add, delete, change) - Anything more than 1 is many

What is the storage capacity terminology in order?

- byte - kilobyte - megabyte - gigabyte - terabyte - petabyte - exabyte - zettabyte

Which statement is true regarding this query executed on an SQLite database? INSERT INTO Parts (Price, Description) VALUES (35.00, 'Battery'); A. It will add a new row to the table B. it will update an existing row in the table C. it will fail if the table has additional columns besides Price and Description D. it will fail if the Description column is define as a numeric field

A. It will add a new row to the table

What is the correct order of clauses in a SQL query that includes WHERE? A. SELECT, FROM, WHERE B. WHERE, SELECT, FROM C. SELECT, WHERE, FROM D. FROM, WHERE, SELECT

A. SELECT, FROM, WHERE

Select the WHERE clause that returns all records with the text "priority" in the Comment column A. WHERE Comment LIKE '%priority%' B. WHERE Comment LIKE 'priority%' C. WHERE Comment LIKE 'priority' D. WHERE Comment LIKE '%priority'

A. WHERE Comment LIKE '%priority%'

Bryan is 13 years old, Lisa is 15, and Mike is 21. Their ages would most likely be stored in a: A. column B. row C. schema D. table

A. column

Which records will be shown when the query ends with LIMIT 10 OFFSET 5? A. records 6 through 15 B. records 5 through 15 C. records 5 through 10 D. records 6 through 10

A. records 6 through 15

The "Items" table has one "Name" column with 12 items on it. What will this query return? *SELECT 'Name' FROM Items;* A. the text "Name" showing 12 times B. the values of all 12 items C. the text "Name" showing once D. the text "Items" showing once

A. the text "Name" showing 12 times

Which SQL query demonstrates a common mistake? A.) SELECT * FROM Bank Deposits; B.) SELECT * FROM Accounts WHERE Name='John Smith'; C.) DELETE FROM Accounts WHERE AccountID IS NULL; D.) SELECT * FROM `Bank Deposits`;

A.) SELECT * FROM Bank Deposits;

To sort a list of students in descending order by last name, you can use this statement: A.) SELECT FirstName, LastName FROM Student ORDER BY LastName DESC; B.) SELECT FirstName, ORDER BY LastName DESC; C.) SELECT FirstName, LastName FROM Student ORDER BY LastName; D.) SELECT FirstName, LastName FROM Student WHERE ORDER BY LastName DESC;

A.) SELECT FirstName, LastName FROM Student ORDER BY LastName DESC;

What does a record represent in a database? A. a kind of information B. a set of information C. a type of field D. a table of values

B. a set of information

As a database administrator, you receive a request to store images in a column. Which SQL data type is the best choice for storing this data? A. Numbers B. binary C. date/time D. text

B. binary

How many clauses are defined in this statement? *SELECT Height, Weight FROM Shapes WHERE Material='Wood' ORDER BY Height;* A. one B. four C. three D. five

B. four

This statement will return a row for every row in the Customer table, and also associated information from the CustomerDetail table is there is any A.) SELECT * FROM CustomerDetail RIGHT JOIN CustomerDetail ON Customer.Id = CustomerDetail.CustomerId; B.) SELECT * FROM Customer LEFT JOIN CustomerDetail ON Customer.Id = CustomerDetail.CustomerId; C.) SELECT * FROM Customer WHERE CustomerDetail.CustomerId = Customer.Id; D.) SELECT * FROM Customer;

B.) SELECT * FROM Customer LEFT JOIN CustomerDetail ON Customer.Id = CustomerDetail.CustomerId;

What is the output of the following statement? SELECT 2*(5+1); A. 8 B. 0.333 C. 12 D. 11

C. 12

Choose the correct statement that returns the students from all states except New York A.) SELECT FirstName, LastName FROM Student WHERE State>= 'NY'; B.) SELECT FirstName, LastName FROM Student WHERE State IS 'CA' AND NOT 'NY'; C.) SELECT FirstName, LastName FROM Student WHERE State != 'NY'; D.) SELECT FirstName, LastName FROM Student WHERE State = 'CA', 'GA', 'NH';

C. SELECT FirstName, LastName FROM Student WHERE State != 'NY';

Which statement is true regarding the DB Browser tool? A. The tool must connect to a database server B. This tool must be purchased C. This tool works consistently across all major platforms D. This tool does not offer a log of historical commands

C. This tool works consistently across all major platforms

Is it possible to group by two fields, and if so what would be the correct syntax? A. Yes, using this syntax: GROUP BY Field1 GROUP BY Field2 B. No, applying multiple grouping in the same statement is not allowed C. Yes, using this syntax: GROUP BY Field1, Field2 D. Yes, multiple grouping is allowed, but only if the grouped fields have the same data type

C. Yes, using this syntax: GROUP BY Field1, Field2

What does the VARCHAR data type usually store? A. a varying sequence of true or false values B. names of variables used within SQL C. text within variable length D. numbers with low precision

C. text within variable length

Which SQL clause will you add to this query in order to associate the "Phone" field with the "Number1" field in the "Contacts" table? SELECT * FROM Customers A.) JOIN Contacts ON Phone=Number1 B.) JOIN ON Customers.Phone = Contacts.Number1 C.) JOIN Contacts ON Customers.Phone = Contacts.Number1 D.) CONNECT Customers.Phone = Contacts.Number1

C.) JOIN Contacts ON Customers.Phone = Contacts.Number1

Which query will return the highest sale amount for every office? A.) SELECT Office FROM Sales GROUP BY MAX(Amount); B.) SELECT MAX(Amount, Office) FROM Sales; C.) SELECT MAX(Amount) FROM Sales GROUP BY Office; D.) SELECT MAX(Amount), Office FROM Sales;

C.) SELECT MAX(Amount) FROM Sales GROUP BY Office;

If you are writing a statement that returns all the fields in a table, which wildcard would you use? A. ; B. & C. @ D. *

D. *

Which data row will be left out with this condition in your statement? *WHERE Color != 'B' OR (Size='L' AND Price>20)* A. Color is 'B', Size is 'L' and Price is 30 B. Color is 'W', Size is 'S' and Price is 20 C. Color is 'G', Size is 'L' and Price is 25 D. Color is 'B', Size is 'XL' and Price is 10

D. Color is 'B', Size is 'XL' and Price is 10

Your SQL statement is returning a TransactionDate field. What can you add to it in order to list the dates from latest to earliest? A. ORDER BY TransactionDate CURRENT B. ORDER BY TransactionDate ASC C. ORDER BY TransactionDate D. ORDER BY TransactionDate DESC

D. ORDER BY TransactionDate DESC

Which type of join returns only the matches for items that are in both tables? A. outer join B. right join C. left join D. inner join

D. inner join

What is the DISTINCT clause used for? A. to sort the values in a column in a certain order B. to count how many rows are represented in the data C. to return the number of characters in a data value D. to return the unique values from a column

D. to return the unique values from a column

What are the dual roles of SQL as a data manipulation language (DML) and as a data definition language (DDL)?

DML - Edit data in the database - Create, read, update, or delete (CRUD) records DDL - Edit the structure (schema) of the database - Add, change, or remove fields or tables

What is the entity-relationship data model?

Entities - A thing to track ---Order, customer, salesperson, item, volunteer, donation Attributes - Describe characteristics of entity ---OrderNumber, CustomerNumber, VolunteerName, PhoneNumber Identifier - Uniquely identifies one entity instance from other instances ---Student_ID_Number

define application programs

Provide security, data consistency, special purpose processing, Ex) handle out-of-stock situations

What is a database management system (DBMS)?

Software program to create, process, administer a database Licensed from vendors - IBM, Microsoft, Oracle, & others DB2, Access, SQL Server, Oracle Database Open Source MySQL: License-free for most applications

What are the keywords in this statement? and what do keywords do? SELECT first_name, last_name FROM quiz_results WHERE state_code = 'CA' ORDER BY last_name ASC;

SELECT FROM WHERE ORDER BY ASC - Keywords tell the database to take some action

define queries

Search using values provided by user

define form

View data; insert new, update existing, delete existing data