Chapter 4 - Introduction to SQL Syntax & Queries

Column Family Explanation Many columns can be tied to a single row key; it groups related data together.

In a _____ database, data is stored in groups of columns 1. Column Family 2. Key-Value 3. Document 4. Graph

Comma (,) Explanation Clauses in SQL statements such as the SELECT, may include more than one field as part of the search. Each field is distinguished from the other by using a comma (,) as a delimiter.

In an SQL SELECT statement, what character is used as a delimiter between fields? 1. Period (.) 2. Interrogative (?) 3. Dash (-) 4. Comma (,)

1986 Explanation SQL became an ANSI standard in 1986. It was created in the 1970's, became an ISO standard in 1987, and became commonly accepted Post-1987.

In what year(s) did SQL become an ANSI standard? 1. Post-1987 2. 1986 3. 1970's 4. 1987

Graph Explanation A relational model has relationships, but the settings are fairly rigid. The graph database allows multiple relationships, and different types of relationships between entities. It is also flexible enough to adapt on the fly without an enormous effort to change the structure.

A _____ database stores information about relationships and entities 1. Key-Value 2. Graph 3. Document 4. Column Family

key value Explanation Unlike a relational model, the key-value data store doesn't care what is inside the bucket. As long as there is a key, e.g., Customer ID, the client/developer can store anything in that bucket, from customer demographics to orders to saved items. The database doesn't what or or what data type is stored.

A _____ is probably the simplest NoSQL solution 1. Column Family 2. Graph 3. Document 4. Key-Value

A query in which parameters or values may change Explanation If you don't know the parameters or values (those things in the SELECT and WHERE statements), then dynamic SQL might be useful. However, if the query is too large and has too many parameters, it may drag down the system.

A dynamic SQL query would be beneficial in which scenario? 1. A query with 10000 lines 2. A query in which parameters or values may change 3. A query with the same values run each time 4. A query used only by one person

All of the above Explanation A query can create a table, insert data, delete a table or object, or retrieve data from a database.

A query can perform the following: 1. SELECT 2. All of the above 3. UPDATE 4. INSERT INTO

d. ponumber: poitem c0001 01 c0001 02 c0001 02 c0002 01 c0003 01 c0003 02

Consider the table given below. For the statement, 'SELECT DISTINCT ponumber, poitem FROM podelivery', which is the correct result set?

SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery GROUP BY ponumber, poitem ORDER BY ponumber, poitem; Explanation The correct answer is SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery GROUP BY ponumber, poitem ORDER BY ponumber, poitem; In he other choices a syntax error will be generated.

Consider the tables given below. The first table is named PODelivery. The second table contains the summarized information from the first table. The quantity delivered for each ponumber/poitem is summarized and shown. Which SQL statement will accomplish this? 1. SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery GROUP BY poitem ORDER BY poitem; 2. SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery GROUP BY ponumber ORDER BY ponumber; 3. SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery GROUP BY ponumber, poitem ORDER BY ponumber, poitem; 4. SELECT ponumber, poitem, SUM(quantityDelivered) AS 'Qty Delivered' FROM podelivery

SELECT * FROM CountryCode WHERE countrycode LIKE 'U%' Explanation SELECT * FROM CountryCode WHERE countrycode = 'U' will only select records where the countrycode is one character wide with the value U. SELECT * FROM CountryCode WHERE countrycode(1) = 'U' will generate a syntax error alert when executed. However, the LEFT function could be used to achieve the correct results. SELECT * FROM CountryCode WHERE LEFT(countrycode,1) = 'U'.

Consider the tables shown below. The first table is the original table, named CountryCode that lists all Country Codes and Country names, and the second table list out all countries with the CountryCode starting with the character 'U'. Which SQL statement can accomplish this? 1. SELECT * FROM CountryCode WHERE countrycode EQUAL 'U' 2. SELECT * FROM CountryCode WHERE countrycode(1) = 'U' 3. SELECT * FROM CountryCode WHERE countrycode LIKE 'U%' 4. SELECT * FROM CountryCode WHERE countrycode = 'U'

SELECT * FROM podelivery WHERE ponumber LIKE 'C%' ORDER BY ponumber DESC, poitem DESC, dateofdelivery DESC Explanation In the correct answer, notice that in the output, both the ponumber and poitem fields are listed in descending order. We also select PO numbers that start with C. The output is sorted first according to the ponumber field and the highest value appears first (i.e. C0003 down to 'C0001' ), then the poitem is listed with the highest value appearing first (i.e. '02' to '01'). In this scenario, the DESC option has to be added to both fields. All other options do not have this, thus they are incorrect.

Consider the two tables given below. The first table is the original table named podelivery. Select the correct SQL SELECT statement that will give you the output as shown in the second table? 1. SELECT * FROM podelivery WHERE ponumber LIKE 'C%' ORDER BY ponumber, poitem DESC 2. SELECT * FROM podelivery WHERE ponumber LIKE 'C%' ORDER BY ponumber DESC, poitem DESC, dateofdelivery DESC 3. SELECT * FROM podelivery WHERE ponumber LIKE 'C%' ORDER BY poitem, ponumber 4. SELECT * FROM podelivery WHERE ponumber LIKE 'C%' ORDER BY ponumber DESC, poitem

Any database server Explanation The beauty of dynamic SQL is that it can be written in any database or server. The syntax may be a little different, but the concepts are the same.

Dynamic SQL can be written on _____ 1. MS Access or SQL Server Only 2. MySQL and SQL Server Only 3. SQL Server only 4. Any database server

productID Explanation The parameter is defined with an ampersand (@); the productID is entered somewhere else, most likely by the user. In this sense the SQL code is dynamic since the system doesn't know the value of the productID until the code runs.

Examine the following snippet from a dynamic SQL statement. Which field is dynamic? 1. productName 2. product 3. tblProducts 4. productID

Document-based Explanation A simple example would be a record about an album, but you can embed objects within database objects, generating very complex data structures.

For deep, incredibly complex data sets, the _____ database is an applicable solution. 1. Key-Value 2. Document-based 3. Column Family 4. Graph

Not Only SQL Explanation With the boom in web applications and large, unstructured data, an alternative to the relational database management system was developed. These Not-only-SQL databases are known as NoSQL because you don't necessarily need SQL to access them. Some can be queried using web tools, such as JavaScript

NoSQL can be referred to as: 1. Not Only SQL 2. SQL Undefined 3. No SQL 4. Only SQL

SQL Injection Explanation A hacker can use SQL Injection to insert/inject bogus or dangerous information into the SQL statement: this forces the application to run SQL you don't want to run (e.g., deleting data!)

One of the security vulnerabilities of dynamic SQL is _____ 1. SQL Injection 2. IP Spoofing 3. Wormholes 4. DNS attack

Relational Explanation Oracle, DB2, and MySQL are databases that maintain a set of separate files (tables), having related data elements in multiple tables. This type of database is called a relational database.

Oracle, DB2, and MySQL are examples of what type of database? 1. Online 2. Knowledge-based 3. Relational 4. Hierarchical

Structured Query Language Explanation The Acronym SQL stands for Structured Query Language. The other answers are bogus.

The Acronym SQL stands for? 1. Superior Question List 2. Structured Query Language 3. Structured Question List 4. Statistical Query Language

FROM Explanation The FROM clause in an SQL statement is used to specify the table or tables suspected of containing the target data subject to retrieval.

The SQL clause used to identify the table or tables containing the target data is the _____ clause. 1. ORDER BY 2. SELECT 3. WHERE 4. FROM

WHERE Explanation The WHERE clause is optional; however, many programmers use the WHERE clause to specify exactly what the target data is. For example, if a programmer is searching for a type of car, he/she may say Where CarType = 'Ford';

The SQL statement clause that is used to identify the criteria to be matched for inclusion in the results is the _____ clause. 1. SELECT 2. FROM 3. ORDER BY 4. WHERE

Structured Query Language Explanation The meaning of SQL helps to reinforce that it is fairly rigid.

What does SQL stand for? 1. Strict Query Language 2. Structured Question Language 3. Strict Question Language 4. Structured Query Language

Transact-SQL Explanation T-SQL stands for Transact-SQL. Transaction-SQL and Transaction Structured Query Language are bogus phrases.

What does T-SQL stand for? 1. Transact-SQL 2. None of the answers are correct. 3. Transaction-SQL 4. Transaction Structured Query Language

More servers increase capacity; NoSQL Explanation NoSQL is only horizontally scalable, meaning that to add capacity, you have to add new servers.

What does horizontally scalable mean? And which does it apply to, SQL or NoSQL? 1. More servers increase capacity; NoSQL 2. More processing power increases capacity; NoSQL 3. More servers increase capacity; SQL 4. More processing power increases capacity; SQL

Select the fields in the table that matches the criteria specified in the query Explanation The select statement is an SQL query that selects all the data from the tables that matches the criteria specified in the query.

What does the Select statement in a SQL query do? 1. Select the fields in the table that matches the criteria specified in the query 2. Select the rows in the table that does not match the criteria specified in the query 3. Select all the fields in the table 4. Select all the rows in a table

Performance impacts Explanation The larger the query, and the more parameters, the more the machine/server has to crunch. This can really drag down performance. Thus it would negatively impact user experience, especially if they have to wait code to process. Or if they have to provide numerous parameter values.

What is a negative effect of having a very large dynamic SQL query? 1. Size is restricted to 1000 lines 2. Performance impacts 3. Easy to debug 4. User-friendliness

Data is stored in multiple tables Explanation A relational database is a database where data is stored in multiple tables. An SQL queries is used to interact with the database, not to store data.

What is a relational or SQL database? 1. Data is stored in multiple flat files 2. Data is stored in multiple computers 3. Data is stored in multiple tables 4. Data is stored in multiple SQL queries

NoSQL can be built faster Explanation Without as much structure, NoSQL can be assembled quickly.

What is an important advantage of NoSQL over SQL in the beginning? 1. NoSQL can be built faster 2. SQL is easier to set up in the long run 3. NoSQL can be built by anyone 4. There is no real advantage

Data about a single event is stored in a single table Explanation A flat database is a table where all the information about any one event or transaction is stored in a single row in one large single table. SQL cannot be used to retrieve data from a flat database.

What is the characteristic of a flat database? 1. Data about a single event is stored in a single computer 2. Data about a single event is stored in a single table 3. Data about multiple events is stored in multiple rows 4. Data about multiple events is retrieved using a single SQL

There are no commands to be executed. Explanation The NULL; statement indicates that there are no statements to be executed.

What is the meaning of the NULL; statement in PL/SQL? 1. The query result does not have any results. 2. There are no commands to be executed. 3. The exception is not listed. 4. The declaration has no variables.

Single line comment Explanation Two hyphens at the start of a statement indicates a single line comment. The statement following the comment delimiter is not executed.

What is the meaning of the following delimiter in PL/SQL? -- 1. Single line comment 2. Item separator 3. End of statement 4. Multi-line comment

The field that has unique, non-repeating data Explanation In any relational database, there will be at least one field that has unique, non-repeating data. This field is the primary key.

What is the primary key in a database? 1. The field that has unique, non-repeating data 2. The field that has repeating data 3. The field that has repeating rows 4. The field that has unique, non-repeating rows

SELECT * FROM tblArtist Explanation If the query has no additional qualifications (e.g., WHERE artistName = 'Journey'), and you use the wildcard (*), ALL rows will be shown. Careful! If the database is very large, this may take a long time or even cause the tool (e.g., Microsoft Access) to crash.

Which SQL statement would return ALL records from the artist table? 1. SELECT artistName FROM tblArtist WHERE artistName='Journey' 2. SELECT * FROM tblArtist WHERE artistID = 17 3. SELECT * FROM tblArtist 4. SELECT *.*

At least one statement within the Begin and End blocks Explanation There has to be at least one statement within the Begin and End; blocks. If there is not statement there has to be a ; to indicate a null statement.

Which of the following IS a required component of the PL/SQL block? 1. At least one Declaration block 2. At least one Comments block 3. At least one statement within the Begin and End blocks 4. At least one Exception block

INSERT Explanation INSERT is the SQL command that can be used to add information to a database. CREATE, ALTER, and USE perform other operations.

Which of the following SQL commands can be used to add information to a database? 1. ALTER 2. USE 3. INSERT 4. CREATE

UPDATE Explanation UPDATE is the SQL command that changes information already in a database. SELECT retrieves information, CREATE generate new objects, and INSERT adds new information.

Which of the following SQL commands changes information already in a database? 1. UPDATE 2. INSERT 3. CREATE 4. SELECT

Target Explanation Target is the company that has not got an RDBMS product offering. Microsoft, IBM, and Oracle do.

Which of the following companies has NOT got an RDBMS product offering? 1. Target 2. Oracle 3. Microsoft 4. IBM

Procedural Language Features Explanation Procedural language features deals with grouping steps together in a unit. String processing deals with sequences of text, date processing deals with dates, and local variables define named memory locations.

Which of the following deals with grouping steps together in a unit? Local Variables 1. Procedural Language 2. Features 3. String Processing 4. Date Processing

SQL can be used to duplicate a record Explanation SQL can be used to change an existing record, add a new record, or to delete a record.

Which of the following is NOT a characteristic of SQL? 1. SQL can be used to add a new record 2. SQL can be used to delete a record 3. SQL can be used to change an existing record 4. SQL can be used to duplicate a record

Changes to the INSERT command. Explanation Changes to the INSERT command is NOT a difference T-SQL and SQL. There were not changes to the INSERT command in T-SQL.

Which of the following is NOT a difference between T-SQL and SQL? Procedural language features. 2. Local variables. 3. Changes to the DELETE command. 4. Changes to the INSERT command.

All of these answers are correct. Explanation Theme oriented, ordered, and many examples exist, are all characteristics of a database.

Which of the following is a characteristic of a database? 1. Theme Oriented 2. Ordered 3. All of these answers are correct. 4. Many Examples Exist

DECLARE <declaration section> BEGIN <executable command(s)> EXCEPTION <exception handling> END; Explanation The PL/SQL statement block starts with a declaration section, followed by a Begin and End; block. The Exception code is within the Begin and End; block.

Which of the following is the correct syntax for a PL/SQL statement?

It is a query language for Oracle databases. Explanation PL/SQL is a query proprietary programming language for Oracle databases used to send query commands to the database and return the results of the commands.

Which of the following is true about PL/SQL? 1. It is a query language for all databases. 2. It is a query language for Oracle databases. 3. It is a programming language for PL databases. 4. It is a programming language for SQL databases.

SQL help is more readily available Explanation SQL is widely used - even most people who use NoSQL have a working knowledge of SQL.

Which of these is an advantage of SQL in the long run compared to NoSQL? 1. SQL help is more readily available 2. NoSQL crashes frequently 3. There is no real advantage 4. SQL is easier to use for everyone

It is useful with object-oriented programming Explanation While ease is a matter of opinion, NoSQL was built with object-oriented programming in mind.

Which of these is true about NoSQL? 1. It is useful with object-oriented programming 2. It is easy for anyone who can type to learn 3. It is more rigid 4. It is faster

Syntax Explanation SQL is a programming language designed to retrieve data stored in a database and requires strict adherence to order and structure. If the syntax is not followed exactly, the computer will not recognize the instruction as a query and will not execute properly.

Which term below refers to strict structural patterns when related to SQL? 1. Standard Query Language 2. Syntax 3. Relationship 4. Database

SQL can be used to interact with the database and the tables Explanation A relational database is also called the SQL database because SQL can be used to interact with the database and tables for retrieving data and creating tables and databases.

Why is the relational database also known as the SQL database? 1. A relational database stores SQL values 2. SQL can be used to interact with the database and the tables 3. SQL can be used to convert a relational database to a flat database 4. SQL can be use in place of a primary key

CONCAT and TRIM Explanation The correct answer is the first option. The TRIM function will remove all trailing blanks from the values of the lastName and firstName fields. Then, the CONCAT function is applied by combining the values from the lastName field to the ',' and finally the values of the firstName field.

You have been provided the table below. The field names are studentID, firstName and lastName and all are of type nchar(20). You need to combine the lastName and firstName fields to give the full name of the students. For example, the full name of the studentID should be displayed as 'Bates, Bernice'. Which two SQL functions used together will produce the correct results? Note: the name should not have any trailing spaces. 1. CONCAT and SQUEEZE 2. CONCAT and TRIM 3. ADD and TRIM 4. MERGE and TRIM

UPDATE Explanation Example, to fix Journey's misspelling: UPDATE tblArtist SET artistName = 'Journey' WHERE artistName='Jrney'

_____ can be used to change data in a record. 1. DELETE 2. UPDATE 3. INSERT 4. SELECT

INSERT INTO Explanation To insert data, use INSERT INTO. Example: INSERT INTO tblArtist(artistName,genre,country) VALUES ('Enya', 'Newage','Ireland')

_____ will add a new row to the table. 1. INSERT INTO 2. DELETE 3. SELECT 4. UPDATE

DELETE Explanation The following would delete all records in the artist table: DELETE * FROM tblArtist

_____ will remove data from the table or database. 1. ERASE 2. DELETE 3. REMOVE 4. WIPE