SQL, Tables, Queries

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/36

flashcard set

Earn XP

Description and Tags

Week 2

37 Terms

1
New cards

Who proposed the relational database model, and when?

Dr. Edgar Codd, in 1970 at IBM

2
New cards

In relational terminology, what are columns and rows also called?

Columns = attributes/fields

Rows = records/tuples

3
New cards

What does each table, row, columns represent?

TABLE: one entity type such as customer or product
ROW: represent instance of that type of entity

COLUMNS: represent values attributed to that instance

4
New cards

What is a Row?

a dataset representing a single item

tuple or record

5
New cards

Column

a labeled element of a tuple e.g. address or DOB

Attribute/field

6
New cards

Table

a set of tuples sharing the same attributes; a set of cols and rows

relation or base relvar

7
New cards

View or Result set

Any set of tuples: a data report from the RDBMS in response to a query

derived relvar

8
New cards

Tuple

unique and unordered

Field properties: named, typed, single-valued

9
New cards

What are the required properties of a good Primary key?

Uniquely identifies one row, is constant over time, and is never NULL

10
New cards

What is a Foreign key?

One or more fields in a child table that identify a single row in another (parent) table

11
New cards

In table design, when a relationship is many-to-many, what should you create?

A junction (bridge) table

12
New cards

SQL is described as a declarative (nonprocedural language. What does that mean?

You specify WHAT result you want, not HOW to get it

13
New cards

What are the types of SQL statements?

Schema (DDL)

Data (DML)

Transaction Control (TCL)

Data Control (DCL)

14
New cards

Explain all the purposes of these SQL statements: DDL, DML, TCL, DCL

DDL - define structures (create, alter, drop)

DML - manipulate data (select, insert, update, delete)

TCL - transactions (commit, rollback)

DCL - permissions (grant, revoke)

15
New cards

List the clauses of a SELECT statement in their written order.

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

16
New cards

What terms specify a column or expression using shorthand?

SELECT t1.*, t2*

FROM t1 INNER JOIN t2

OR it can be a built in function call:
SELECT AVG(score), t1.* FROM t1 ..,

17
New cards

Can a select expr be given a column alias?

Yes used as the expression’s columns name

SELECT CONCAT(last_name,’,’,first_name) AS full_name FROM mytable ORDER BY full_name

18
New cards

Why can’t you refer to a column alias inside a WHERE clause?

Bc the column’s value/alias might not yet be determined when WHERE is evaluated

19
New cards

What happens when you name more than one table?

you are performing a join

for each table specified you can also specify a table alias

20
New cards

How do you use SELECT with columns from a Single Table

SELECT SKU, SKU_description, Department, Buyer

FROM SKU_DATA;

21
New cards

What do the SQL wildcard characters and % represent in a LIKE pattern?

_ = exactly one unspecified character;

% = any sequence of characters (including none)

22
New cards

What is the difference between the WHERE and HAVING clauses?

WHERE determines which rows form the groups; HAVING determines which groups appear in the final result

23
New cards

In MySQL, when you CREATE TABLE enrollments with PRIMARY KEY (student_id, course_id, semester), what doest his create?

A composite primary key made from all three columns together

24
New cards

What happens if you try to INSERT a row with a student_id that already exists as a Primary Key value?

MySQL rejects it with a ‘Duplicate entry’ error

25
New cards

Does every relational table need to have a Primary key?

No - MySQL allows tables without a PK, though it is generally undesirable for entity tables

26
New cards

What is the difference between DROP and TABLE and DELETE FROM (with no WHERE)?

DROP TABLE removes the table structure entirely; DELETE removes rows but keeps the table

27
New cards

GROUP BY

groups data by column values

28
New cards

HAVING

What does it do and how is it typically formed?

specifies conditions on groups, formed by the GROUP BY clause

29
New cards

ORDER BY

sorts the result set using either raw column data or expressions of them; ASC, DESC

30
New cards

How can columns selected for output be referred to in ORDER BY and GROUP BY clauses

using column names, column aliases, or column positions (in the select_expr)

31
New cards

What is the general structure of RDBMS

Underlying storage devices holding the actual data

Set of executing processes providing access to the data

Schema (DDL) and SQL code (DML)

Known as the DB instance

All of it referred to as the database server

32
New cards

MySQL

databases are used everywhere

simple and easy to use

open-source software

implements the relational model

designed for storing structured data

feature-rich SQL support

part of the LAMP stack

33
New cards

What are the MySQL column data types?

Columns store data, so they have data types associated

Character Types

Numeric

Date (temporal) - several types and formats

Other: BLOB - Binary Large Object and TEXT

34
New cards

Character types

CHAR - fixed length

VARCHAR - variable length

ENUM - finite set of values

35
New cards

Numeric types

integer - several ranges

floating-point - two ranges, may specify precision and scale

36
New cards

What is the MySQL Client?

The MySQL command-line interface (cli) is called mysql

It is a simple SQL tool with input line editing capabilities

37
New cards

What are some cli options?

database - the database to use

execute - execute the statement and quit

user - MySQL username to use when connecting to server

password - password to use when connecting to the server