InLearning - Essential SQL

Question 1

CRUD

Answer 1

Create, Read, Update, Delete

Question 2

SQL

Answer 2

Structured Query Language

Question 3

DSL

Answer 3

Domain Specific Language

Question 4

GPL

Answer 4

General Purpose Language

Question 5

RDBMS

Answer 5

Relational Data Base Management System

Question 6

RDSMS

Answer 6

Relational Data Stream Management System

Question 7

DSMS

Answer 7

Data Stream Management System

Question 8

an SQL statement is terminated with a

Answer 8

;

Question 9

ASCII

Answer 9

American Standard Code for Information Exchange

Question 10

single quotes are for

Answer 10

alias identifier

Question 11

How do you pick columns?

Answer 11

SELECT

Question 12

How do select rows?

Answer 12

WHERE

Question 13

What is dynamic memory?

Answer 13

Dynamic memory allocation is when an executing program requests that the operating system give it a block of main memory. The program then uses this memory for some purpose. Usually the purpose is to add a node to a data structure.

Question 14

SID

Answer 14

Security Identifier

Question 15

ANSI

Answer 15

American National Standards Institute

Question 16

ISO

Answer 16

Interational Organization for Standardization

Question 17

SQL was developed at ___ by ___.

Answer 17

IBM, Chamberlain & Boyce

Question 18

Who did the relational model?

Answer 18

Tedd Codd

Question 19

SEQUEL

Answer 19

Structured English Query Language

Question 20

What was Oracle’s first name?

Answer 20

Relational Software Inc,

Question 21

A table has rows and columns, where rows represent ___ and columns represent the ___.

Answer 21

A table has rows and columns, where rows represent records and columns represent the attributes.

Question 22

Tuple

Answer 22

Tuple − A single row of a table, which contains a single record for that relation is called a tuple.

In mathematics, a tuple is a finite ordered list (sequence) of elements. Mathematicians usually write tuples by listing the elements within parentheses “ {\displaystyle ({\text{ }})} (\text{ })” and separated by commas; for example, {\displaystyle (2,7,4,1,7)} (2, 7, 4, 1, 7) denotes a 5-tuple.

Question 23

Schema

Answer 23

The term “schema” refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).

A representation of a plan or theory in the form of an outline or model.

Question 24

ALTER TABLE Statement is used to ___, ___, or ___ ___ in an existing table.

Answer 24

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Question 25

How do you create rows of data

Answer 25

INSERT INTO test (a,b) VALUES (10,’a’);

Question 26

How do you create an ID Column?

Answer 26

INTEGER PRIMARY KEY

Question 27

What is a Boolean data type?

Answer 27

This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return aBoolean data type are known as Boolean expressions. Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

Question 28

BOOLEAN

Answer 28

This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions. Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

Question 29

What is the wild card?

Answer 29

% at beginnning or end

Question 30

How to use underscore.

Answer 30

before a letter _a (means second letter is a)

Question 31

WHERE clause allows you to get just the ___ you need

Answer 31

rows

Question 32

DISTINCT

Answer 32

to find distinct values

Question 33

four kinds of JOIN

Answer 33

Inner, Outer, Left, Right

Question 34

Alias Identifier syntax

Answer 34

SELECT * FROM table_name [AS] alias_name

Question 35

A table alias is also called a ___.

Answer 35

A table alias is also called a correlation name.

Question 36

What is a CLAUSE?

Answer 36

???

Question 37

junction table

Answer 37

???

Question 38

LENGTH

Question 39

SUBSTR

Answer 39

unpack data from a column

Question 40

TRIM

Question 41

how to view source

Answer 41

CTRL+U

Question 42

INSERT INTO

Answer 42

The INSERT INTO statement is used to insert new records in a table.

Question 43

UPPER and LOWER funtions

Answer 43

make capital or lower case

Question 44

use of operator “=”

Question 45

what are the two number types

Answer 45

integer and real

Question 46

floating point numbers sacrifice

Answer 46

something for percision

Question 47

TYPEOF

Question 48

catenate/concatenate

Question 49

CAST

Question 50

MODULUS operator

Question 51

ROUND

Question 52

SQL Standard date & time formate

Question 53

SQL Lite

Question 54

COUNT

Answer 54

aggregate???

Question 55

HAVING clause

Question 56

TRANSACTION

Question 57

TRIGGER

Question 58

A database is a collection of information

Question 59

NULL means?

Answer 59

Nothing here.

Question 60

The COUNT() function returns the number of rows that matches a specified criteria.

Answer 60

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Question 61

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Question 62

HAVING syntax

Answer 62

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Question 63

ORDER BY Syntax

Answer 63

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

Question 64

LENGTH

Answer 64

counts numbers or characters

Question 65

SUBSTING (?, ?, ?)

Question 66

Alias

Answer 66

AS

Question 67

You can’t create a field w/o giving it a ___.

Answer 67

Data Type

Question 68

Dates are tricky because ___.

Answer 68

Different formats.

Question 69

Your database has a table called orders, containing a field called order_num. Which is a correctly formed query in MySQL?

Answer 69

select order_num from orders

Question 70

What does zero-indexing mean in a database?

Answer 70

Database software typically starts counting rows at zero, irrespective of the id number stored in the row.

Question 71

The following statement will return all orders other than the one with an id of 5 (in MySQL): select * from orders where order_id<>5

Answer 71

TRUE

Question 72

What would the following query return? SELECT order_id FROM orders WHERE order_num<5 AND order_type=’online’ OR order_type=’cash’

Answer 72

Rows with both id<5 and order_type of ‘online’, plus all rows with order_type of ‘cash’ regardless of id.

Question 73

What’s the best description of an SQL string function?

Answer 73

It is like a formula, applied to a field to change some or all characters within all rows returned, e.g. to make all characters upper case.

Question 74

Assuming a table called cities contains a field called city, what will the following query return in MySQL? SELECT SUBSTRING(city,1,1) FROM cities WHERE city=”London”One or more rows containing the letter L.

Answer 74

One or more rows containing the letter L.

Question 75

Using an alias on a field name can change the column heading in the result set.

Answer 75

TRUE

Question 76

Databases store data, plus data about the data. One of the key kinds of latter data is data type. There are scores of data types. The database will not let you create a field without its data type.

Answer 76

TRUE

Question 77

In SQL queries it’s rare to specify a WHERE clause in this format (MySQL): WHERE field_name = ‘2016-07-07’. Why?

Answer 77

Because we don’t know what kind of date field_name is: it might require a time as well as date, in which case we would get a false result.

Question 78

Aggregation functions

Question 79

SELECT

FROM

GROUP BY

Question 80

SELECT DISTINCT

Answer 80

to removed repeated values

Question 81

How do you find how many customers you have?

Answer 81

SELECT DISTINCT customers

FROM table1

Question 82

Why is the following query poorly formed? SELECT *, count(*) FROM address

Answer 82

Because it mixes grouped and ungrouped results. Reading across the single row of results will suggest the count value belongs to that row.

Question 83

Which of these queries is in the right order?

Answer 83

SELECT district, count(district) as ct FROM address WHERE district like ‘%B%’ GROUP BY district HAVING count(district)>8 ORDER BY ct DESC

Question 84

SELECT DISTINCT is used to return unique values. You can use it to return unique combinations by listing the relevant fields after the word DISTINCT, separated by commas.

Answer 84

TRUE

Question 85

INSERT INTO syntax

Answer 85

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

Example:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);

Question 86

ALTER TABLE syntax

Answer 86

ALTER TABLE table_name
ADD column_name datatype;

Question 87

What SQL statement extracts data from a database?

Answer 87

SELECT

Question 88

Which SQL statement is used to update data in a database?

Answer 88

UPDATE

Question 89

Which SQL statement is used to delete data from a database?

Answer 89

SELECT

Question 90

LIMIT

Question 91

JOIN

Question 92

If you JOIN a 5 row table to a 6 row table, why might you only get 4 rows?

Answer 92

Because those are the only rows that are the same.

Question 93

JOIN and ON are part of the ___ section of the query.

Answer 93

FROM

Question 95

JOIN merges ___, UNION merges ___.

Answer 95

columns, rows

Question 96

The IN operator allows you to specify multiple values in a ___ clause.

Answer 96

The IN operator allows you to specify multiple values in a WHERE clause.

Question 97

IN Syntax

Answer 97

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);

Question 98

A field in one table that appears as a field in another table is probably ___.

Answer 98

A field in one table that appears as a field in another table is probably a foreign key .

Question 99

There is usually only one primary key in a table. It is typically has data type set to ___ and is set to ___.

Answer 99

There is usually only one primary key in a table. It is typically has data type set to integer (int) and is set to auto-increment .

Question 100

To make sense of an unfamiliar database, it’s helpful to visualise it by following the trail of ___.

Answer 100

To make sense of an unfamiliar database, it’s helpful to visualise it by following the trail of foreign keys .

Question 101

Different types of join determine which rows are included from which tables. To return rows that exist in both tables you would use a(n) ___.

Answer 101

Different types of join determine which rows are included from which tables. To return rows that exist in both tables you would use a(n) INNER JOIN .

Question 102

T/F. UNION will display rows from both queries, one under the other. Unique values are returned with UNION while all values are returned with UNION ALL.

Answer 102

TRUE

Question 103

T/F. We can return the same results as JOIN using IN like so: SELECT * from table where field2 in (SELECT field2 from table2)

Answer 103

FALSE

Question 104

A VIEW is a ___.

Answer 104

stored query

Question 106

Which is the best definition of a view in SQL?

Answer 106

A stored query, which runs and returns a tabular result set when you click on it or refer to it in a query. Like a virtual table.

Question 107

Which is the best definition of a function in SQL?

Answer 107

A stored calculation, which returns one or more values when you refer to it in a query.

Question 108

Which is the best definition of a stored procedure in SQL?

Answer 108

A stored query, which returns a value or performs an action, when called in a query.

Question 109

ACID compliant

Question 110

normalized