MySQL

Question 1

My SQL Comments

Answer 1

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

Question 2

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

Answer 2

SELECT * FROM MyTable

Question 3

SELECT

Answer 3

Select the following columns for display in the query

Question 4

AS

Answer 4

Specify aliases/names for the columns SELECT Name AS Country

Question 5

ORDER BY

Answer 5

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

Question 6

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

Answer 6

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

Question 7

WHERE

Answer 7

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

Question 8

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

Answer 8

WHERE Population<1000

Question 9

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

Answer 9

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

Question 10

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

Answer 10

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

Question 11

INSERT

Answer 11

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

Question 12

How can you insert a new row into a table?

Answer 12

INSERT INTO test VALUES (1,2,3)

Question 13

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

Answer 13

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

Question 14

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

Answer 14

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

Question 15

UPDATE

Answer 15

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

Question 16

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

Answer 16

UPDATE test SET c=NULL WHERE a=2

Question 17

DELETE

Answer 17

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

Question 18

How can you delete all rows in a table?

Answer 18

DELETE FROM test

Question 19

DROP TABLE

Answer 19

Deletes table from database

Question 20

How can you delete a table from a databse?

Answer 20

DROP TABLE

Question 21

How can you concatenate strings in MYSQL?

Answer 21

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

Question 22

NULL

Answer 22

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

Question 23

CREATE DATABASE

Answer 23

Creates a new database

Question 24

How can you create a new database?

Answer 24

CREATE DATABASE newdatabase

Question 25

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

Answer 25

USE newdatabase

Question 26

USE

Answer 26

Selects/activates a database

Question 27

CREATE TABLE

Answer 27

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

Question 28

DESCRIBE

Answer 28

Shows the structure of a table DESCRIBE test

Question 29

SHOW TABLE STATUS

Answer 29

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’

Question 30

DROP TABLE IF EXISTS

Answer 30

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

Question 31

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

Answer 31

DESCRIBE test

Question 32

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

Answer 32

SHOW TABLE STATUS

Question 33

How can you delete a table if it exists?

Answer 33

DROP TABLE IF EXISTS test

Question 34

SHOW CREATE TABLE

Answer 34

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

Question 35

What is an index?

Answer 35

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.

Question 36

How can you add an index?

Answer 36

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

Question 37

Multicolumn indexes

Answer 37

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

Question 38

DROP INDEX

Answer 38

Used to delete an index DROP INDEX ab ON testtable

Question 39

How can you delete an index?

Answer 39

DROP INDEX ab ON testtable

Question 40

How can you name an index?

Answer 40

INDEX ab (a,b)

Question 41

What are constraints?

Answer 41

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

Question 42

NOT NULL

Answer 42

Constraint. Disallows NULL values in the column

Question 43

UNIQUE

Answer 43

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

Question 44

DEFAULT

Answer 44

Sets the default value of a field.

Question 45

PRIMARY KEY

Answer 45

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

Question 46

AUTO_INCREMENT

Answer 46

automatically adds entries sequentially

Question 47

SERIAL

Answer 47

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

Question 48

LAST_INSERT_ID()

Answer 48

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

Question 49

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

Answer 49

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

Question 50

FOREIGN KEY

Answer 50

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

Question 51

ALTER TABLE

Answer 51

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

Question 52

How can you add columns to a table?

Answer 52

ALTER TABLE test ADD col VARCHAR(10)

Question 53

How can you delete columns in a table?

Answer 53

ALTERTABLE test DROP col

Question 54

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

Answer 54

ALTER TABLE test ADD col VARCHAR(10) AFTER a

Question 55

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

Answer 55

ALTER TABLE test ADD col VARCHAR(10) FIRST

Question 56

What are the types of MYSQL data types?

Answer 56

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

Question 57

Aggregate function that concatenates all values in a column.

Answer 57

GROUP_CONCAT(Column)

GROUP_CONCAT(Column SEPARATOR ‘/’)

GROUP_CONCAT(DISTINCT Column SEPARATOR ‘/’)

Question 58

CASE

Answer 58

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

or

CASE a

WHEN 0 THEN ‘false’

WHEN 1 THEN ‘true’

END AS boolA

Question 59

What is a trigger?

Answer 59

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.

Question 60

What is a stored routine?

Answer 60

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.

Question 61

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

Question 62

What is a stored procedure?

Answer 62

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