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
