Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL Final Exam

memorize.ai (lvl 286)

(0)

43 cards

()

Section 1

Preview this deck

(Free Response #2) Conceptually know what an associative entity is. Define it. Tell when you would create an associative entity. When you do create one how do you use composite primary keys? Define a surrogate primary key - what is that?

Front

1 / 43

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 (43)

Section 1

(43 cards)

(Free Response #2) Conceptually know what an associative entity is. Define it. Tell when you would create an associative entity. When you do create one how do you use composite primary keys? Define a surrogate primary key - what is that?

Front

a. <definition> An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances. </definition> An associative entity is created whenever there is a many-to-many relationship between entities. When you define more than one column as your primary key on a table, it is called a composite primary key. A surrogate key is a unique to the entity: The primary key of the associative entity isn't taken from the other two entites.

Back

A client PC that is responsible for processing presentation logic, extensive application, and business rules logic as well as many DBMS functions is called a ___.

Front

Fat Client

Back

The ___ of two tables is a table containing all rows that are in both tables

Front

Intersect

Back

Command to insert into a row

Front

INSERT INTO FICTION VALUES ('9946','Cannery Row','PE',11.95);

Back

___ causes two tables with a common domain to be combined into a single table or view

Front

A join operation

Back

What does UPPER, LOWER, ROUND, TRIM, ADD MONTHS, and FLOOR do?

Front

UPPER: Displays values all in uppercase LOWER: Displays values in all lower case ROUND: Rounds values to a specified number of decimals TRIM: Removes extra spaces following values ADD MONTHS: add specified number of months to a date FLOOR: Removes everything to the right of the decimal point

Back

Code for DELETE command?

Front

Delete From Customer where cust_num = '895';

Back

What is a transaction

Front

A logical unit of work. Sequence of steps that accomplish a task

Back

How does an admin grant and revoke privileges?

Front

grant insert on book to Gomez, Liston; grant update (price) on book to Andrews, Zimmer; Revoke select on all from Andrews;

Back

(Free Response #3) What are some of the responsibilities of a database administrator? Give some examples of commands they use - granting privileges, revoking privileges. They can also create views

Front

a. DBA are responsible for granting, revoking, and creating views that are used in the database. They also are responsible of the management of data resources which includes developing procedures to protect and control data, resolving data ownership, and use issues, conceptual data modeling, and developing maintaining corporate wide data definitions and standards. Some commands are as follows--grant select on book to Rodriquez; --grant insert on book to Gomez, Liston; --grant update (price) on book to Andrews, Zimmer; -Revoke select on all from Andrews;

Back

What is the syntax for an index?

Front

create index book_index1 on book(title);

Back

What is the UNION COMMAND

Front

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

Back

What does it mean to "Commit"?

Front

It basically means saving your database. Auto Commit Command: SET XACT_ABORT ON

Back

To retrieve data from multiple tables in a query you can use the ___ operator with a sub query

Front

IN

Back

(Free Response #1) Define a relational database is and what it means to relate tables together and why we relate tables together

Front

Database that has multiple tables that are connected together, through common fields/keys, primary key in one table and foreign key in the other table. We relate tables together so that we can query information out of the database from more than one table at the same time

Back

What are the functions from chapter 8

Front

UPPER, LOWER, ROUND, TRIM, ADD MONTHS, FLOOR

Back

(bonus) Create a stored procedure

Front

create procedure usp_DISP_author @authornum decimal(2,0) as select author_num, RTRIM(author_first)+' '+RTRIM(author_last) from author where author_num = @authornum;

Back

The main differences between java applets and java services is ___.

Front

servlets are stored on their server while applets are stored on the client

Back

Which of the following have affected the way businesses use computing systems to meet the demand of the competitive market place?

Front

All of these

Back

If you precede the sub query by the ___ operator, the condition is true only if it satisfies one or more values produced by the sub query

Front

ANY

Back

Syntax to create a VIEW

Front

Create view Book_Inventory(branch_num, units) as (select branch.branch_num, sum(on_hand) as UNITS from inventory, branch where inventory.branch_num = branch.branch_num group by branch.branch_num);

Back

Name of person in business in charge of database

Front

Database Admin

Back

Two tables are ___ compatible if they have the same number of columns, and if their corresponding columns have identical data types and lengths

Front

Union

Back

A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a ___

Front

Natural Join

Back

In a ___ all rows from the table on the right will be included regardless of whether they match rows from the table on the left

Front

Right-Outer-Join

Back

The___data type stores only the actual character string

Front

VARCHAR

Back

What is the code to call a sub procedure

Front

exec usp_DISP_ADDAUTHOR 69, 'Robertson', 'Bradley';

Back

A __ is a set of application routines that programs use to direct the performances of procedures by the computers operating system

Front

API

Back

What is the code to join two tables together?

Front

where book.book_code = publisher.book_code;

Back

In which of the following situation would one have to use an Outer-join in order to obtain the desired results?

Front

A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero)

Back

You create an alias by typing the name of the table by pressing the ___ and then typing the name of the alias

Front

Spacebar

Back

A PC configured to handle user interface with littler or no local storage is called a ___

Front

Thin Client

Back

When a sub query involves a table listed in the outer query, the sub query is called a ___ sub query

Front

Correlated

Back

What is a trigger?

Front

stored procedure that executes on it's own

Back

Oracle and SQL server support the ___ operator, but Microsoft access does not

Front

Intersect

Back

Use the___datatype for columns that contain letters and special characters and for columns containing number that will not be used in calculations

Front

CHAR

Back

An operation to join a table to itself is called a ___.

Front

Self Join

Back

In a ___ join, all rows from both tables are included regardless of whether they match rows from the other table

Front

Full Outer

Back

How do you delete a View?

Front

DROP VIEW Book_Inventory;

Back

What is the command for the difference between two tables?

Front

MINUS

Back

You can join tables by using a condition and the ___ clause

Front

Where

Back

Command to create table

Front

Create Table Sales_Branch (Branch_Num decimal(2,0) primary key, Branch_Name char(50) );

Back

A join in which rows that do not have matching values in common columns are still included in the result table, is called ___.

Front

Outer-Join

Back