CS174MIDTERM

> alter a database or table > operates on an existing table to add, change, or delete columns

ALTER

Write a query to add add FULLTEXT index to table classics for columns author and title

ALTER TABLE classics ADD FULLTEXT(author, title);

Write a query to add index to the first 20 characters in each row of the author column in table classics

ALTER TABLE classics ADD INDEX(author(20));

Write a query to add index to year column elements from table classics with no limit

ALTER TABLE classics ADD INDEX(year);

Write a query to add primary key column isbn to table classics

ALTER TABLE classics ADD PRIMARY KEY(isbn);

Write a query to add new column pages to table classics as an unsigned smallint

ALTER TABLE classics ADD pages SMALLINT UNSIGNED;

> Write a query to rename column type to category in table classics as a varchar(16) > CHANGE requires data type to be specified

ALTER TABLE classics CHANGE type category VARCHAR(16);

Write a query to remove id column from table classics

ALTER TABLE classics DROP id;

Write a query to change data type of column year in table classics from CHAR(4) to SMALLINT

ALTER TABLE classics MODIFY year SMALLINT;

Write a query to rename table classics to pre1900

ALTER TABLE classics RENAME pre1900;

Write a query to convert a table tablename to MyISAM engine

ALTER TABLE tablename ENGINE = MyISAM;

causes mySQL to set a unique value for this column in every row

AUTO_INCREMENT

back up a table

BACKUP

create a database

CREATE

Write a query to create a database called publications

CREATE DATABASE publications;

Write an alternative query to adding index to author column in classics table to the first 20 characters

CREATE INDEX author ON classics (author(20));

Write a query to create a table called classics with author (varchar(128)), title (varchar(128)), type (varchar(128)), year (char(4)) on MyISAM engine

CREATE TABLE classics (author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM;

Write a query to create table countries with country_name (varchar(128)), region_id (int(4)), and with unsigned, not null, auto-incrementing country_id column

CREATE TABLE countries ( country_name VARCHAR(128), region_id INT(4) country_id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY);

Write a query to create a table called tablename with a field in it called fieldname of the data type UNSIGNED INTEGER

CREATE TABLE tablename (fieldname INT UNSIGNED);

Write a query to create a table called tablename with a field fieldname; fieldname is padded with zeros if its width is less than 4 characters

CREATE TABLE tablename (fieldname INT(4) ZEROFILL);

'0000-00-00'

DATE

'0000-00-00 00:00:00' (any date)

DATETIME

delete a row from a table

DELETE

Write a query to delete row(s) from table classics with title of Little Dorrit

DELETE FROM classics WHERE title='Little Dorrit';

describe a table's columns

DESCRIBE

Write a query to check whether table classics has been created

DESCRIBE classics;

> delete a database, table or column > irreversible

DROP

Write a query to delete table disposable

DROP TABLE disposable;

exit

EXIT

change user privileges

GRANT

display help

HELP

insert data

INSERT

Write query to insert a new row with: > author: Mark Twain > title: The Adventures of Tom Sawyer > type: Fiction > year: 1876 into table classics

INSERT INTO classics(author, title, type, year) VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');

makes the column take an integer large enough for you to store more than 4 billion records in the table

INT UNSIGNED

> an auto-increment column is useful as a key, because you will tend to search for rows based on this column > each entry in the column id will now have a unique number, with the first starting at 1 and counting up from there

KEY

enables you to choose how many rows to return in a query

LIMIT

lock table(s)

LOCK

lets you enter multiple words in a search query and checks them against all words in the FULLTEXT columns

MATCH...AGAINST

ensures that every column has a value

NOT NULL

same as EXIT

QUIT

rename a table

RENAME

Write a query to get all employee details from the employee table order by first name, descending

SELECT * FROM employees ORDER BY first_name DESC;

Write a query to get all employee details from the employee table order by first name, ascending

SELECT * FROM employees ORDER BY first_name;

Write a query to get the number of employees working with the company from table employees

SELECT COUNT(*) FROM employees;

Write a query to get the number of designations (job_id's) available in the employees table from table employees

SELECT COUNT(DISTINCT job_id) FROM employees;

Write a query to get unique Job ID from employee table.

SELECT DISTINCT job_id FROM employees;

Write a query to return 2 rows from author and title columns, starting from row #1 from the classics table

SELECT author,title FROM classics LIMIT 1,2;

Write a query to return 3 rows from author and title columns from classics table

SELECT author,title FROM classics LIMIT 3;

Write a query to search and return for any combinations of words from author and title that include charles and exclude species

SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('+charles -species' IN BOOLEAN MODE);

Write a query to return anything from columns author and title that contains the word and from table classics

SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('and');

Write a query to return anything from columns author and title where author is matching the string ending with Species from table classics

SELECT author,title FROM classics WHERE author LIKE "%Species";

Write a query to return anything from columns author and title matching the string Charles followed by any other text from table classics

SELECT author,title FROM classics WHERE author LIKE "Charles%";

Write a query to sort all rows by author then by descending year of publication for table classics

SELECT author,title,year FROM classics ORDER BY author, year DESC;

Write a query to get the the number of authors in each category for table classics.

SELECT category,COUNT(author) FROM classics GROUP BY category;

Write a query to get the employee ID, name (first_name, last_name), salary in ascending order of salary from table employees

SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary;

Write a query to display the names of the Employees from employee table.

SELECT first_name,last_name FROM employees;

list details about an object

SHOW

execute a file

SOURCE

display the current status

STATUS

'00:00:00'

TIME

'0000-00-00 00:00:00' (range 1970-2037)

TIMESTAMP

empty a table

TRUNCATE

unlock table(s)

UNLOCK

update an existing record

UPDATE

Write a query to change sets with author name of Mark Twain to Mark Twain (Samuel Langhorne Clemens) for table classics

UPDATE classics SET author='Mark Twain (Samuel Langhorne Clemens)' WHERE author='Mark Twain';

use a database

USE

0000 (year 0000 and 1901-2155)

YEAR

cancel input

\c