InLearning - Essential SQL Flashcards by sophanet phoeun

CRUD

Create, Read, Update, Delete

How well did you know this?

Not at all

Perfectly

SQL

Structured Query Language

How well did you know this?

Not at all

Perfectly

DSL

Domain Specific Language

How well did you know this?

Not at all

Perfectly

GPL

General Purpose Language

How well did you know this?

Not at all

Perfectly

RDBMS

Relational Data Base Management System

How well did you know this?

Not at all

Perfectly

RDSMS

Relational Data Stream Management System

How well did you know this?

Not at all

Perfectly

DSMS

Data Stream Management System

How well did you know this?

Not at all

Perfectly

an SQL statement is terminated with a

;

How well did you know this?

Not at all

Perfectly

ASCII

American Standard Code for Information Exchange

How well did you know this?

Not at all

Perfectly

single quotes are for

alias identifier

How well did you know this?

Not at all

Perfectly

How do you pick columns?

SELECT

How well did you know this?

Not at all

Perfectly

How do select rows?

WHERE

How well did you know this?

Not at all

Perfectly

What is dynamic memory?

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.

How well did you know this?

Not at all

Perfectly

SID

Security Identifier

How well did you know this?

Not at all

Perfectly

ANSI

American National Standards Institute

How well did you know this?

Not at all

Perfectly

ISO

Interational Organization for Standardization

How well did you know this?

Not at all

Perfectly

SQL was developed at ___ by ___.

IBM, Chamberlain & Boyce

How well did you know this?

Not at all

Perfectly

Who did the relational model?

Tedd Codd

How well did you know this?

Not at all

Perfectly

SEQUEL

Structured English Query Language

How well did you know this?

Not at all

Perfectly

What was Oracle’s first name?

Relational Software Inc,

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

Tuple

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.

How well did you know this?

Not at all

Perfectly

Schema

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.

How well did you know this?

Not at all

Perfectly

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

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.

How well did you know this?

Not at all

Perfectly

How do you create rows of data

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

How do you create an ID Column?

INTEGER PRIMARY KEY

What is a Boolean data type?

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.

BOOLEAN

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.

What is the wild card?

% at beginnning or end

How to use underscore.

before a letter \_a (means second letter is a)

WHERE clause allows you to get just the ___ you need

rows

DISTINCT

to find distinct values

four kinds of JOIN

Inner, Outer, Left, Right

Alias Identifier syntax

SELECT \* FROM table\_name [AS] alias\_name

A table alias is also called a \_\_\_.

A table alias is also called a correlation name.

What is a CLAUSE?

???

junction table

???

LENGTH

SUBSTR

unpack data from a column

TRIM

how to view source

CTRL+U

INSERT INTO

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

UPPER and LOWER funtions

make capital or lower case

use of operator "="

what are the two number types

integer and real

floating point numbers sacrifice

something for percision

TYPEOF

catenate/concatenate

CAST

MODULUS operator

ROUND

SQL Standard date & time formate

SQL Lite

COUNT

aggregate???

HAVING clause

TRANSACTION

TRIGGER

A database is a collection of information

NULL means?

Nothing here.

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

SELECT COUNT(column\_name) FROM table\_name WHERE condition;

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

HAVING syntax

SELECT column\_name(s) FROM table\_name WHERE condition GROUP BY column\_name(s) HAVING condition ORDER BY column\_name(s);

ORDER BY Syntax

SELECT column1, column2, ... FROM table\_name ORDER BY column1, column2, ... ASC|DESC;

LENGTH

counts numbers or characters

SUBSTING (?, ?, ?)

Alias

You can't create a field w/o giving it a \_\_\_.

Data Type

Dates are tricky because \_\_\_.

Different formats.

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

select order\_num from `orders`

What does zero-indexing mean in a database?

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

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

TRUE

What would the following query return? SELECT order\_id FROM orders WHERE order\_num\<5 AND order\_type='online' OR order\_type='cash'

Rows with both id\<5 and order\_type of 'online', plus all rows with order\_type of 'cash' regardless of id.

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

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.

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.

One or more rows containing the letter L.

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

TRUE

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.

TRUE

In SQL queries it's rare to specify a WHERE clause in this format (MySQL): WHERE field\_name = '2016-07-07'. Why?

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.

Aggregation functions

SELECT FROM GROUP BY

SELECT DISTINCT

to removed repeated values

How do you find how many customers you have?

SELECT DISTINCT customers FROM table1

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

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

Which of these queries is in the right order?

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

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.

TRUE

INSERT INTO syntax

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');

ALTER TABLE syntax

ALTER TABLE table\_name ADD column\_name datatype;

What SQL statement extracts data from a database?

SELECT

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

UPDATE

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

SELECT

LIMIT

JOIN

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

Because those are the only rows that are the same.

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

FROM

JOIN merges \_\_\_, UNION merges \_\_\_.

columns, rows

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

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

IN Syntax

SELECT column\_name(s) FROM table\_name WHERE column\_name IN (value1, value2, ...);

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

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

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

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 .

To make sense of an unfamiliar database, it's helpful to visualise it by following the trail of \_\_\_.

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

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) \_\_\_.

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 .

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.

TRUE

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

FALSE

A VIEW is a \_\_\_.

stored query

Which is the best definition of a view in SQL?

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.

Which is the best definition of a function in SQL?

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

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

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

ACID compliant

normalized