Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

MySQL Test 1

memorize.ai (lvl 286)

(0)

72 cards

()

Section 1

Preview this deck

USE

Front

1 / 72

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (72)

Section 1

(50 cards)

USE

Front

change default database

Back

intersection

Front

a table which contains all rows that are in the two tables

Back

compound condition

Front

A condition formed by connecting two or more simple conditions

Back

Cartesian Product

Front

the combination of all rows in the first table and all rows in the second table

Back

SELECT

Front

view data in a table

Back

INTERSECT

Front

Operator that creates a temporary table containing all rows that are in both tables

Back

CHAR

Front

Fixed length character data

Back

DESC

Front

operator included in an ORDER BY clause when results are to be sorted in descending order

Back

DDL

Front

Data definition language

Back

VARCHAR

Front

Variable length character data

Back

DBMS

Front

A software program that lets you create a database and then use it to add, change, delete, sort and view the data in database

Back

INSERT

Front

add rows to a table

Back

HAVING

Front

the clause that limits a condition to the groups that are included

Back

UPDATE

Front

change a value in a table

Back

DROP TABLE

Front

drop entire table

Back

DELETE

Front

delete a row in a table

Back

alias

Front

an alternate name for table

Back

EXISTS

Front

Operator that checks for the existence

Back

GROUP BY

Front

the clause that groups rows based on the specific column

Back

data type

Front

type of data that a column can contain as well as the max number of char or digits that the column can store

Back

subquery

Front

inner query in nested queries

Back

product

Front

the combination of all rows in the first table and all rows in the second table

Back

DECIMAL

Front

used to represent decimal number

Back

simple condition

Front

A condition that has the form, column name, comparison operator and either another column name or a value

Back

VALUES

Front

insert values in a table

Back

difference

Front

the set of all rows that are in the first table BUT that are not in the second table

Back

CREATE DATABASE

Front

The SQL command to create a database

Back

LEFT OUTER JOIN

Front

A join in which all rows from the table on the left(first table) will be included regardless of whether they match rows from the table on the right

Back

SHOW COLUMNS

Front

list all the columns in a table

Back

Database

Front

A structure that contains different categories of information and the relationships between these categories

Back

FROM

Front

the clause that indicates the table from which to retrieve the specific columns

Back

COUNT

Front

counts number of rows in a table

Back

INT

Front

used to represent integer values

Back

primary sort key

Front

The most important column to be sorted

Back

aggregate function

Front

Special SQL functions that apply to groups of rows and used to calculate sums, averages, counts, maximum values and minimum values

Back

Structured Query Language

Front

A language used for retrieving and manipulating database data

Back

SQL

Front

Structured Query Language

Back

default database

Front

database to which all subsequent commands pertain

Back

DML

Front

Data manipulation language

Back

WHERE

Front

the clause that specifies any conditions for the query

Back

script file

Front

A file contains one or more SQL commands

Back

Database schema

Front

logical container for the database objects

Back

INNER JOIN

Front

A join that compares the table in the FROM clause and list only those rows that satisfy the condition in the WHERE clause

Back

sort key

Front

the column on which data is to be sorted when the ORDER BY clause is used

Back

DISTINCT

Front

operator that eliminates duplicate values in the results of a query

Back

DESCRIBE

Front

list all column in a table and data types

Back

LIKE

Front

wildcard characters to test for a pattern match WHERE last_name LIKE 'Ch%'; Cho, Chang, Chi..

Back

FULL OUTER JOIN

Front

A join in which all rows from both table will be included regardless of whether they match rows from the other table

Back

grouping

Front

creates groups of rows that shares some common characterastics

Back

computed column

Front

A column that does not exist in the database but can be computed using data in existing columns

Back

Section 2

(22 cards)

Henry Books Database: Problem 2 (p. 159) For each book published by Plume, list the book code, book title, and price.

Front

SELECT BOOK_CODE, TITLE, PRICE FROM BOOK B, PUBLISHER P WHERE B.PUBLISHER_CODE = P.PUBLISHER_CODE AND PUBLISHER_NAME ='Plume';

Back

Alexamara Marina Group Database: Problem 15 (p. 160) Repeat Exercise 14, (List the slip ID, boat name, owner number, service ID, number of estimated hours, and number of spent hours for each service request on which the category number is 2.) but this time be sure each slip is included regardless of whether the boat in the slip currently has any service requests for category 2.

Front

SELECT MS.SLIP_ID, BOAT_NAME, OWNER_NUM, SR.SERVICE_ID, EST_HOURS, SPENT_HOURS FROM SERVICE_REQUEST SR LEFT JOIN MARINA_SLIP MS ON SR.SLIP_ID = MS.SLIP_ID WHERE SR.CATEGORY_NUM = 2;

Back

Alexamara Marina Group Database: Problem 14 (p. 160) List the slip ID, boat name, owner number, service ID, number of estimated hours, and number of spent hours for each service request on which the category number is 2.

Front

SELECT M.SLIP_ID, BOAT_NAME, OWNER_NUM, S.SERVICE_ID, EST_HOURS, SPENT_HOURS FROM SERVICE_REQUEST S, MARINA_SLIP M, SERVICE_CATEGORY C WHERE M.SLIP_ID = S.SLIP_ID AND C.CATEGORY_NUM = S.CATEGORY_NUM AND S.CATEGORY_NUM = 2;

Back

Alexamara Marina Group Database: Problem 3 (p. 160) For every service request for routine engine maintenance, list the slip ID, marina number, slip number, estimated hours, spent hours, owner number, and owner's last name.

Front

SELECT SERVICE_REQUEST.SLIP_ID, MARINA_SLIP.MARINA_NUM, MARINA_SLIP.SLIP_NUM, SERVICE_REQUEST.EST_HOURS, SERVICE_REQUEST.SPENT_HOURS, MARINA_SLIP.OWNER_NUM, OWNER.LAST_NAME FROM SERVICE_REQUEST INNER JOIN SERVICE_CATEGORY ON SERVICE_REQUEST.CATEGORY_NUM = SERVICE_CATEGORY.CATEGORY_NUM INNER JOIN MARINA_SLIP ON SERVICE_REQUEST.SLIP_ID = MARINA_SLIP.SLIP_ID INNER JOIN OWNER ON MARINA_SLIP.OWNER_NUM = OWNER.OWNER_NUM WHERE (SERVICE_CATEGORY.CATEGORY_DESCRIPTION = 'Routine engine maintenance');

Back

Henry Books Database: Problem 3 (p. 159) List the book title, book code, and price of each book published by Plume that has a book price of at least $14.

Front

SELECT B.TITLE, BOOK_CODE, PRICE FROM BOOK B INNER JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE WHERE PUBLISHER_NAME = 'Plume' AND PRICE >= 14;

Back

Premiere Products Database: Problem 8 (p. 158) Repeat Exercise 7, (For each order, list the order number, order date, part number, part description, and item class for each part that makes up the order.) but this time order the rows by item class and then by order number.

Front

SELECT ORDERS.ORDER_NUM, ORDER_DATE, PART.PART_NUM, DESCRIPTION, CLASS FROM ORDERS, ORDER_LINE, PART WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM AND ORDER_LINE.PART_NUM = PART.PART_NUM ORDER BY CLASS, ORDERS.ORDER_NUM;

Back

Premiere Products Database: Problem 14 (p. 158) List the order number and order date for each order that contains an order line for an Iron.

Front

SELECT ORDER_NUM, ORDER_DATE FROM ORDERS WHERE ORDER_NUM IN (SELECT ORDER_NUM FROM ORDER_LINE WHERE PART_NUM IN (SELECT PART_NUM FROM PART WHERE DESCRIPTION = 'Iron'));

Back

Alexamara Marina Group Database: Problem 8 (p. 160) Repeat Exercise 7, (List the boat name, owner number, owner last name, and owner first name for each boat in marina 1.) but this time only list boats in 30-foot slips.

Front

SELECT M.BOAT_NAME, O.OWNER_NUM, LAST_NAME, FIRST_NAME FROM MARINA_SLIP M,OWNER O WHERE M.OWNER_NUM = O.OWNER_NUM AND LENGTH = '30';

Back

Premiere Products Database: Problem 10 (p. 158) Repeat Exercise 9, (Use a subquery to find the rep number, last name, and first name of each sales rep who represents at least one customer with a credit limit of $5,000. List each sales rep only once in the results.) but this time do not use a subquery.

Front

SELECT DISTINCT R.REP_NUM, LAST_NAME, FIRST_NAME FROM REP R, CUSTOMER C WHERE R.REP_NUM = C.REP_NUM AND CREDIT_LIMIT = 5000;

Back

Henry Books Database: Problem 17 (p. 159) List the book code, book title, and units on hand for each book in branch number 2. Be sure each book is included, regardless of whether there are any copies of the book currently on hand in branch 2. Order the output by book code.

Front

SELECT BOOK.BOOK_CODE, BOOK.TITLE, INVENTORY.ON_HAND FROM BOOK LEFT JOIN INVENTORY ON ( BOOK.BOOK_CODE = INVENTORY.BOOK_CODE AND INVENTORY.BRANCH_NUM = 2 ) ORDER BY BOOK.BOOK_CODE;

Back

Henry Books Database: Problem 6 (p. 159) Find the book title for each book written by author number 18. Use the IN operator in your formulation.

Front

SELECT TITLE FROM BOOK WHERE BOOK_CODE IN (SELECT BOOK_CODE FROM WROTE WHERE AUTHOR_NUM IN (SELECT AUTHOR_NUM FROM AUTHOR WHERE AUTHOR_NUM = 18));

Back

Premiere Products Database: Problem 15 (p. 158) List the order number and order date for each order that either was placed by Johnson's Department Store or that contains an order line for a Gas Range.

Front

SELECT O.ORDER_NUM, ORDER_DATE FROM ORDERS O INNER JOIN CUSTOMER C ON O.CUSTOMER_NUM = C.CUSTOMER_NUM WHERE C.CUSTOMER_NAME ='Johnson''s Department Store' UNION SELECT O.ORDER_NUM, ORDER_DATE FROM ORDERS O INNER JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM INNER JOIN PART P ON OL.PART_NUM = P.PART_NUM WHERE P.DESCRIPTION = 'Gas Range';

Back

Henry Books Database: Problem 10 (p. 159) Find the book title, author last name, and units on hand for each book in branch number 4.

Front

SELECT B.TITLE, A.AUTHOR_LAST, I.ON_HAND FROM BOOK B INNER JOIN INVENTORY I ON I.BOOK_CODE = B.BOOK_CODE INNER JOIN BRANCH BR ON BR.BRANCH_NUM = I.BRANCH_NUM INNER JOIN WROTE W ON W.BOOK_CODE = B.BOOK_CODE INNER JOIN AUTHOR A ON A.AUTHOR_NUM = W.AUTHOR_NUM WHERE BR.BRANCH_NUM = '4';

Back

Henry Books Database: Problem 12 (p. 159) Find the book code and book title for each book whose price is more than $10 or that was published in Boston.

Front

SELECT BOOK_CODE, TITLE FROM BOOK B, PUBLISHER P WHERE B.PUBLISHER_CODE = P.PUBLISHER_CODE AND PRICE > 10 OR CITY='BOSTON';

Back

UNION

Front

Operator that creates a temporary table containing every row that is in either the first table, the second table or both tables.

Back

Premiere Products Database: Problem 11 (p. 158) Find the number and name of each customer that currently has an order on file for a Gas Range.

Front

SELECT C.CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER C INNER JOIN ORDERS O ON C.CUSTOMER_NUM = O.CUSTOMER_NUM INNER JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM INNER JOIN PART P ON OL.PART_NUM = P.PART_NUM WHERE DESCRIPTION = 'Gas Range';

Back

Premiere Products Database: Problem 9 (p. 158) Use a subquery to find the rep number, last name, and first name of each sales rep who represents at least one customer with a credit limit of $5,000. List each sales rep only once in the results.

Front

SELECT DISTINCT REP_NUM, LAST_NAME, FIRST_NAME FROM REP WHERE REP_NUM IN (SELECT REP_NUM FROM CUSTOMER C WHERE C.CREDIT_LIMIT = 5000);

Back

Henry Books Database: Problem 14 (p. 159) Find the book code and book title for each book whose price is more than $10 but that was not published in Boston.

Front

SELECT BOOK_CODE, TITLE FROM BOOK B, PUBLISHER P WHERE B.PUBLISHER_CODE = P.PUBLISHER_CODE AND PRICE > 10 AND P.CITY != 'BOSTON';

Back

Henry Books Database: Problem 5 (p. 159) List the book title for each book that has the type PSY and that is published by Jove Publications.

Front

SELECT B.TITLE FROM BOOK B INNER JOIN PUBLISHER P ON B.PUBLISHER_CODE = P.PUBLISHER_CODE WHERE B.TYPE = 'PSY' AND P.PUBLISHER_NAME ='Jove Publications';

Back

Alexamara Marina Group Database: Problem 1 (p. 160) For every boat, list the marina number, slip number, boat name, owner number, owner's first name, and owner's last name.

Front

SELECT M.MARINA_NUM, SLIP_ID, BOAT_NAME, O.OWNER_NUM,FIRST_NAME, LAST_NAME FROM MARINA_SLIP M, OWNER O WHERE M.OWNER_NUM = O.OWNER_NUM;

Back

Henry Books Database: Problem 13 (p. 159) Find the book code and book title for each book whose price is more than $10 and that was published in Boston.

Front

SELECT BOOK_CODE, TITLE FROM BOOK B JOIN PUBLISHER P ON P.PUBLISHER_CODE = B.PUBLISHER_CODE WHERE B.PRICE > 10 AND P.CITY = 'BOSTON';

Back

What are the title(s) and price(s) of the least expensive book(s) in the database?

Front

SELECT TITLE, PRICE FROM BOOK WHERE PRICE = (SELECT MIN(PRICE)FROM BOOK);

Back