spend 1-2 minute and write a MySQL query that comes into your mind.
SELECT * FROM `moviedb`.`Actor` WHERE STATE= ‘NY’.
I hope this is a good query in terms of finding actors within the movie database that are from the state of New York.
good query, don’t forget the “;” which ends the command!
Select count (*) From `CustomerDB`.`Contact`
This query count the number of record in the contact Table.
don’t forget the “;” which ends the command!
select distinct first_name, count(first_name) from moviedb.actor group by first_name;
Lists unique first names of actors in alphabetical order and counts how many occurrences of that name there are.
Lists unique first names of actors in “alphabetical order”, then it should be “order by”!
SELECT * FROM moviedb.actor WHERE first_name= ‘John’;
This action would list all of the actors with the first name John in this database
nice with no issue!
select * from moviedb.actor where last_name=’Zero’;
And you need to type return key + Ctrl (command) key to run the query.
select avg(`grade`) from `mis2502`.`student`;
This allows you to see the average grade of all your students in mis2502
SELECT MAX(`Price`) FROM `m1orderdb`.`Product`
This function allows you to see the max price of a product. There are also other functions that allow you to find the min, average and sum.
right, a better way to phrase the objective may be: find the price of the most expensive product.
SELECT * FROM .customer WHERE = ‘NJ’
This selects all customers from NJ
right, this not only selects all customers, it selects all the columns as well.
select sum ( price) from m1orderdb. `product`
select * from moviedb.actor order by last_name
this selects all records on the table and orders by lname
Select*From’moviedb’.'state Where Actor=PA;
This is a queryto find a state where actors from PAare located. Hopefully I did it correctly.
the correct way to write this query is:
Select * From ’moviedb’.’actor’ Where state=’PA’;
first, the table is ACTOR, so it needs to be immediately after the “from” keyword, second, “PA” is a string value, so in where clause, it needs to have single quotes.
instead of using the database name before each table name, before entering any commands, the following “USE databasename” could be used to simplify future queries.
also, when comparing strings in the WHERE clause, double quotes can also be used instead of single quotes.
Yes Uriel you are correct. In many versions of workbench, putting “use database_name” will simplify future queries, but it may not work in some cases.
Yes double quotes work same as single quotes for string values in MySQL.
SELECT * FROM moviedb.actor WHERE last_name=’Verna’;
SELECT AVERAGE (`Price`) FROM `m1orderdb`.`Product`;
To find the average price of a product
This is actually try to get the average price of all products.
SELECT min(`Price`) FROM `m1orderdb`.`Product`
SELECT COUNT(*) FROM MOVIEDB.ACTOR
Count, I believe, is one of the more useful tools in SQL that will be commonly used to retrieve analytical data in the business world (not to say others like AVG and MIN/MAX aren’t).
SELECT * FROM `order` WHERE order_id= (101,102, 103);
This will give you all the orders that have an order id that is 101, 102, or 103
Nicole you are actually getting into the idea of “sets” in the query, the correct way to do it is follows:
SELECT * FROM `order` WHERE order_id IN (101,102, 103); so it is an “IN” clause. Please try different variations to see if this one works.
select count(*) from moviedb.film where rating=”PG-13″;
This function counts the number of movies containing “PG-13″ in the rating on the film table.
SELECT * FROM moviedb.rental, moviedb.customer WHERE rental.customer_id = customer.customer_id AND first_name=’MARY’ AND last_name=’SMITH’
This query accesses the “moviedb” schema and joins the “rental” and “customer” tables to provide information on all of Mary Smith’s purchases.