MySQL Flashcards

1
Q

My SQL Comments

A

–this is a comment #this is a non-standard SQL comment /* multi line comment */

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How can you show all columns of a table named MyTable?

A

SELECT * FROM MyTable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SELECT

A

Select the following columns for display in the query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

AS

A

Specify aliases/names for the columns SELECT Name AS Country

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ORDER BY

A

Sort based on a column. ORDER BY Continent To sort in ascending/descending order ORDER BY Continent ASC ORDER BY Continent DESC To sort on one criteria then another (two levels): ORDER BY Continent, Name To sort in different sort orders ORDER BY Continent DESC, Region, Name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How can you do multi-level sorting? (First by one column, then another?)

A

To sort on one criteria then another (two levels): ORDER BY Continent, Name To sort in different sort orders ORDER BY Continent DESC, Region, Name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

WHERE

A

Filters results. Takes a Boolean expression and returns the row only if the expression is true. WHERE Population)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How can you return rows in a query that matches a condition?

A

WHERE Population<1000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can you return rows in a column that have a value that matches a pattern?

A

WHERE Name LIKE ‘%island%’ WHERE Name REGEXP ‘^.[a-e].*$’ %=wildcard - = any character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How can you return rows in a column that match values in a list only?

A

WHERE CONTINENT IN (‘Europe’, ‘Asia’)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

INSERT

A

Insert new rows into table INSERT INTO test VALUES (1,2,3) To only insert into specified columns INSERT INTO test(b,c) VALUES (2,3) To insert values from a query INSERT INTO test(a,b,c) SELECT id, name, description FROM item

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How can you insert a new row into a table?

A

INSERT INTO test VALUES (1,2,3)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can you insert values from a query into a tabe?

A

To insert values from a query INSERT INTO test(a,b,c) SELECT id, name, description FROM item

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How can you only insert into specified columns of a table?

A

INSERT INTO test(b,c) VALUES (2,3)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

UPDATE

A

Change data in particular columns/rows as specified by WHERE UPDATE test SET c=NULL WHERE a=2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How can you change data in particular columns/rows of a table?

A

UPDATE test SET c=NULL WHERE a=2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

DELETE

A

Deletes/removes rows from a table DELETE FROM test WHERE a=2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How can you delete all rows in a table?

A

DELETE FROM test

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

DROP TABLE

A

Deletes table from database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How can you delete a table from a databse?

A

DROP TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How can you concatenate strings in MYSQL?

A

Strings (in single quotes/double quotes) are separated by spaces for concatenation. ‘hello’ ‘world’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

NULL

A

Represents the lack of a value. Cannot be tested for by using the equal sign since it has no value. a=NULL –invalid To test for equality: a IS NULL -or- a IS NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

CREATE DATABASE

A

Creates a new database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

How can you create a new database?

A

CREATE DATABASE newdatabase

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

How can you select/activate a database for further operations?

A

USE newdatabase

26
Q

USE

A

Selects/activates a database

27
Q

CREATE TABLE

A

Creates a new table CREATE TABLE test( id INTEGER, name VARCHAR(255), state CHAR(2) );

28
Q

DESCRIBE

A

Shows the structure of a table DESCRIBE test

29
Q

SHOW TABLE STATUS

A

Shows the tables inside the database and their status SHOW TABLE STATUS To see just the details/status of one table: SHOW TABLE STATUS LIKE ‘test’

30
Q

DROP TABLE IF EXISTS

A

Drops the table if it already exists DROP TABLE IF EXISTS test

31
Q

How can you show the schema/structure of a table?

A

DESCRIBE test

32
Q

How can you show the tables inside a database and their status?

A

SHOW TABLE STATUS

33
Q

How can you delete a table if it exists?

A

DROP TABLE IF EXISTS test

34
Q

SHOW CREATE TABLE

A

Shows the create table statement for creating an identical table SHOW CREATE TABLE

35
Q

What is an index?

A

A data structure that optimizes searches, sequential access, insertions, and deletions. - Keeps a logical order for a set of data. - organizes indexes via B-trees.

36
Q

How can you add an index?

A

CREATE TABLE( id INTEGER, a VARCHAR(255), INDEX(a) );

37
Q

Multicolumn indexes

A

INDEX (a, b) A multicolumn index is an index that based on more than one column (e.g. a and b). They can be used for lookups in queries that specify values in a known range for combinations of A and B values, or for queries that specify just an A value/column (leftmost in the index). SELECT * FROM table WHERE a=1 AND b=2 SELECT * FROM table WHERE a=4 The index would NOT work for: SELECT * FROM table WHERE b=2

38
Q

DROP INDEX

A

Used to delete an index DROP INDEX ab ON testtable

39
Q

How can you delete an index?

A

DROP INDEX ab ON testtable

40
Q

How can you name an index?

A

INDEX ab (a,b)

41
Q

What are constraints?

A

Constraints are used to define specific rules and behaviors for some of your columns. 1. NOT NULL 2. UNIQUE 3. PRIMARY KEY: NOT NULL + UNIQUE 4. AUTO_INCREMENT 5. SERIAL: PRIMARY KEY + AUTO_INCREMENT

42
Q

NOT NULL

A

Constraint. Disallows NULL values in the column

43
Q

UNIQUE

A

Constraint. Only unique values are allowed. An index is created (Key = UNI) to enforce the uniqueness. **BUT, since NULL is not a value, adding multiple NULL values will NOT give an integrity constraint error. To prevent this, you should use UNIQUE NOT NULL

44
Q

DEFAULT

A

Sets the default value of a field.

45
Q

PRIMARY KEY

A

Constraint that includes NOT NULL and UNIQUE, and provides an index named “primary”. - only one primary key allowed per table - does not require that the ID column uses a primary key constraint

46
Q

AUTO_INCREMENT

A

automatically adds entries sequentially

47
Q

SERIAL

A

Column with both AUTO_INCREMENT and PRIMARY_KEY Type of column is bigint(20) unsigned

48
Q

LAST_INSERT_ID()

A

Function that returns the last global insert’s ID SELECT LAST_INSERT_ID();

49
Q

How can you make values in a column rely on the available keys in another table?

A

FOREIGN KEY (a_id) REFERENCES tablea(id) - adding values that do not exist in the referenced table/column will cause an error - deleting a row/key in the parent/referenced table that is depended on/used is also forbidden

50
Q

FOREIGN KEY

A

Declares a column that relies on a foreign key in another table. FOREIGN KEY (c_id) REFERENCES client(id) - adding values that do not exist in the referenced table/column will cause an error - deleting a row/key in the parent/referenced table that is depended on/used is also forbidden

51
Q

ALTER TABLE

A

Alter a table (e.g. add/delete columns) after it has been defined. ALTER TABLE test ADD col VARCHAR(10) ALTER TABLE test DROP col ALTER TABLE test ADD col VARCHAR(10) AFTER a

52
Q

How can you add columns to a table?

A

ALTER TABLE test ADD col VARCHAR(10)

53
Q

How can you delete columns in a table?

A

ALTERTABLE test DROP col

54
Q

How can you add a column in a particular position of the table?

A

ALTER TABLE test ADD col VARCHAR(10) AFTER a

55
Q

How can you add a column to the beginning of a table?

A

ALTER TABLE test ADD col VARCHAR(10) FIRST

56
Q

What are the types of MYSQL data types?

A
  1. Numeric
    1. Integer | TINY INT | SMALLINT | MEDIUMINT | INTEGER | BIGINT
    2. Fixed Point | DECIMAL(#significant digits, position of decimal point from right)
    3. Floating Point | FLOAT/REAL | DOUBLE
  2. Strings
    1. Character Strings | CHAR(fixedlength) | VARCHAR(maxlength)
    2. Binary Strings | BINARY(fixedlength) | VARBINARY(maxlength)
    3. Large Object Storage | TINYBLOB/TEXT | BLOB/TEXT | MEDIUMBLOB/TEXT | LONGBLOB/TEXT
  3. Dates and Times
    1. DATE | YYYY-MM-DD
    2. DATETIME | YYYY-MM-DD HH:MM:SS
    3. TIMESTAMP | YYYY-MM-DD HH:MM:SS
    4. TIME | HH:MM:SS or HHH:MM:SS
    5. YEAR | YYYY
  4. Lists/ Enumeration
    1. ENUM(list)
    2. SET(list)
  5. Boolean
57
Q

Aggregate function that concatenates all values in a column.

A

GROUP_CONCAT(Column)

GROUP_CONCAT(Column SEPARATOR ‘/’)

GROUP_CONCAT(DISTINCT Column SEPARATOR ‘/’)

58
Q

CASE

A

CASE WHEN a THEN ‘true’ ELSE ‘false’ END AS boolA

or

CASE a

 WHEN 0 THEN 'false'

 WHEN 1 THEN 'true'

END AS boolA

59
Q

What is a trigger?

A

An operation that’s automatically performed when a specific database event occurs. Commonly used to update a table whenever a row is inserted/updated in another table.

60
Q

What is a stored routine?

A

A stored routine is a set of SQL statements that are stored on the database server and can be used by any client with permission to use them.

There are two kinds of stored routines. (1) Stored functions and (2) stored procedures.

61
Q

A stored function returns a value and is used int the context of an expression.

A
62
Q

What is a stored procedure?

A

A stored procedure is essentially a stored series of MySQL commands/a query. It is used like a complete query and is called separetely using the call statement.

DELIMITER // CREATE PROCEDURE total\_duration(IN a VARCHAR(255), OUT d VARCHAR(255)) BEGIN SELECT tracklen(SUM(duration)) INTO d FROM track WHERE album\_id IN(SELECT id FROM album WHERE artist LIKE a) ; END // CALL total\_duration('%hendrix', @dur);