MySQL

What is a Primary Key?

A field that contains a unique value in each field

What is a SELECT statement?

A statement that retrieves data from the database.

What does the WHERE Clause do?

Acts as filter in a SELECT statement. This clause specifies a condition that can be evaluated as either true or false.

What does the SUM function do?

Calculates the total of a group of values

CREATE TABLE employee( ID int auto_increment, EmployeeFirstName varchar(30), EmployeeLastName varchar (30), Job varchar(30), PRIMARY KEY (ID) );

Create the syntax to create this table

What is the syntax to delete a table

Drop tablename

What does the AVG function do?

Finds the average value from a group of values

What does the MIN function do?

Finds the lowest value from a group of values

What is the Syntax to sort results from a-z?

ORDER BY fiedname ASC

What is the Syntax to sort results from z-a?

ORDER BY fieldname DESC

A database schema consists of the following tables: Category(categoryid, CategoryName) Blog(Blogid, categoryid, Blogtitle,DateCreated) Write the MySQL to show the Blog ID, Blog Title, Category Name of the earliest blog that was published. (4 marks)

SELECT BLOGID, BlogTitle, CategoryName FROM blog INNER JOIN category ON category.categoryId=blog.categoryId HAVING min(DateCreated);

A database schema consists of the following tables: Category(categoryid, CategoryName) Blog(Blogid, categoryid, Blogtitle,DateCreated) Write the MySQL to display all Category Names (even if they don't have any associated blogs), Blog Titles and the Date they have been created sort by ascending order of Blog Title.

SELECT CategoryName, BlogTitle, DateCreated FROM category LEFT JOIN blog ON category.categoryId=blog.categoryId ORDER BY BlogTitle ASC

A table called world contains a field called population, create a select statement that will display the total of the population field.

SELECT SUM(population) FROM world

What two clauses are mandatory in a SELECT statement?

SELECT and FROM

A table called word contains the fields continent, country and population. Create a SELECT query that will display each continent and the number of countries that have a population of at least 10000000 in each continent. (4 marks)

SELECT continent, COUNT(country) FROM world WHERE population >=10000000 GROUP BY continent

Create the syntax to return the id, name and billaddr fields from the table customers where the name is either Jones or Smith

SELECT id, name, billaddr FROM customers WHERE name="jones" or name="smith"

Create the syntax to return the name and price fields from the items table where the price is between £5 and £10

SELECT name, price FROM items WHERE price >=5 and price <=10

Create the syntax to return the name and type fields from the items table where the name starts with 'things'

SELECT name, type FROM items WHERE name LIKE "things%"

Create the syntax to return the title, created and content fields from the table entries where the id=254

SELECT title, created, content FROM entries WHERE id=524;

What is an Inner Join?

This JOIN selects only those records from the database tables that have matching values.

What is a Left Join?

This JOIN will return all rows from the left table (this is the table you specify first in your code) and any matching records from the right table (this is the table you specify second in your code)

What is a Right Join?

This JOIN will return all the rows from the right table (this is the table you specify second in your code) and any matches from the left table (this is the table you specify first in your code).

What does the COUNT function do?

This function counts the number of values in a group

What is the LIKE operator?

This operator allows you to search for a pattern in a string in which portions of the string value are represented by wildcard characters.

SELECT author.AuthorFirstName,author.AuthorLastName, book.BookTitle FROM author INNER JOIN book ON author.AuthorID = book.authorID;

Write the MySQL to display the authors name and the books they have written

CREATE TABLE author( AuthorId INT auto_increment, AuthorFirstName varchar(30), AuthorLastName varchar (30), PRIMARY KEY (AuthorId) )ENGINE=INNODB; CREATE TABLE book( ISBN varchar(30), AuthorId INT, BookTitle varchar(30), Price DECIMAl(10,2), PRIMARY KEY (AuthorId), FOREIGN KEY (AuthorId) REFERENCES author (AuthorId) )ENGINE=INNODB;

a) Write the MySQL to implement the above database schema, this should include Primary and Foreign Key constraints