• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

image

PLAY BUTTON

image

PLAY BUTTON

image

Progress

1/53

Click to flip

Card Range To Study

through

53 Cards in this Set

  • Front
  • Back
How do you connect to a mysql server?
mysql -h host -u user -p
How do you disconnect from a mysql server?
mysql> QUIT
What is the command to find out what databases exist on a server?
mysql> SHOW DATABASES;
How do you switch to a database?
mysql> USE nameofdatabase;
How do you grant all permissions to a user to a database?
mysql>GRANT ALL ON databasename.* TO 'your_mysql_name'@'your_client_host';
Are database names case sensitive?
Yes.
How do you list the tables inside a database?
mysql> SHOW TABLES;
Whats the command to create a table?
mysql> CREATE TABLE name ( ... columns ...)
Whats the query to select the maximum value in a column?
SELECT MAX(colname) as colname from tablename;
How do you select the entire row that contains the maximum value in a column?
SELECT * from tablename where colname = (SELECT MAX(colname) from tablename);
What does "join" table mean?
Making two tables into 1 table, by joining at a column with matching values.
What does "left join" mean?
Means preserve the left table even if no keys match.
What does "right join" mean?
Means preserve the right table even if no keys match.
How do you make a backup of a database?
use the mysqldump command
mysqldump -u root -p[password] [database]>/home/[user]/[database].sql
Give the structure of a query using GROUP BY
SELECT colname, function(colname) FROM tablename GROUP BY colname;
How do you sort the result set of a query by a specified column?
Use ORDER BY

example:
select * from tablename ORDER BY columname DESC
How do you select records in which you want to retrieve the NULL columns only?
select * from table where column IS NULL;
How do you concatenate two strings?
CONCAT(string1, string2, ...)
How do you concatenate two string with separators?
CONCAT_WS(separator, string1, string2, ...)
Whats the mysql function for concatenating strings returned by GROUP BY?
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR ' ')
-> FROM student
-> GROUP BY student_name;
What does the DISTINCT keyword do?
Removes duplicate entries in the column of a result set.
Where are the user defined functions/procedures stored?
In the mysql.proc or mysql.func table
Whats the difference between a procedure a function?
They both can take multiple params. But a function must return 1 value. While a procedure can return 0 or more values.
How do you show the command used to create a PROCEDURE?
mysql> show create procedure nameofprocedure;
How do you delete a PROCEDURE?
mysql> drop procedure nameofprocedure;
How do you show a list of all stored procedures?
mysql> show procedure status;
How do you declare a variable inside a procedure?
DECLARE varname TYPE;
How do you assign a variable inside a procedure?
SET varname = value;
What does a while loop look like inside a stored procedure?
WHILE conditions DO
STUFF HERE;
END WHILE;
What statement allows you to leave a loop inside a procedure?
LEAVE;
What statement brings you back to the start of the loop inside a procedure?
ITERATE;

similar to continue; in java.
What do conditional statements look like inside of procedures?
IF expression THEN commands
ELSEIF expression THEN commands
ELSE commands
END IF;
How do you backup a database?
mysqldump -u username -ppassword database_name > dump.sql
How do you import a database?
mysql -u username -ppassword database_name < dump.sql
What is another word for row?
Record.
What is another name for column?
Field.
What does the unique index do?
Insures none of the records can have the same value.
The relational data model is based on ___________.
a two-dimensional table.
An operation to extract a column is called _________.
projection
What are the 3 types of database models?
1] hierarchical data model
2] network data model
3] relational data model
Selection extracts data _______.
horizontally.
If table 1 has 3 rows, and table 2 has 3 rows and you do a Cartesian product, how many rows does the resulting table have?
3x3 = 9
What is the E-R model?
Entity Relationship model. Entity refers to a recognizable object in the real world. Relationship means how entities are related to each other.
What is normalization?
Removes duplication and minimizes redundant chunks of data resulting in better organization and more effective use of physical space.
Define the 1st Normal Form
Eliminate repeating groups such that all records in all tables can be identified uniquely by a primary key in each table. In other words, all fields other than the primary key must depend on the primary key.
Define the 2nd Normal Form
all non-key values must be fully functionallity dependent on the primary key. No partial dependencies are allowed.
Define the 3rd Normal Form
Eliminate transitive dependencies, meaning that a field is indirectly determined by the primary key.
If we have a table with two int primary keys. Is this combination of records possble?
1,1
1,2
1,3
yes
If we have a table with two int primary keys. Is this combination of records possble?
1,1
1,1
no.
whats the difference between an identifying and non-identifying foreign key?
non-identifying can be NULL, identifying means not null(primary key)
Explain 1 to 1 relationship
one row in table A maps to one row in table B only.
Explain 1 to many relationship
many rows in table A can map to one row in table B.
In a 1:N Relation ship Table A goes to Table B. The crowsfoot symbol is on Table B. How is this read?
Table A can have many of Table B