For all the questions below write SQL query. Submit your queries in one pdf docu

Question

For all the questions below write SQL query. Submit your queries in one pdf document. You only need to submit the query statement, no need to include resultset.

The number at the end of the query statement indicates the number of rows returned by the query.

We are using AdventureWorkd2012 database for this assignment.

1.Show all the stores which have sales person with id 275 or 282 or 288 or 277 or 290(Sales.Store 307)

2. Show all the bike stores worked by sales person id 282 or 275.(eg. a bike store will have the word bike in the name)(Sales.Store 40)

3. For all products which are listed for less than $20 create a list which shows the name product number in one column. (Production.product 215). For Example if the product is id=922 the result will show Road TIre Tube - TT-R982

4. Show all products which start selling within 2 days of june 1, 2002.( Production.Product 211)

5. Show all products that we start selling in June and may.( Production.Product 211)

6. Show the maximum list price, average list price, maximum standard cost and minimum standard cost. Make sure all columns have a headings.(Production.Product 1

Explanation / Answer

2.  An Example for the Beginners (But NOT for the dummies)

A MySQL database server contains many databases (or schemas). Each database consists of one or more tables. A table is made up of columns (or fields) and rows (records).

The SQL keywords and commands are NOT case-sensitive. For clarity, they are shown in uppercase. The names or identifiers (database names, table names, column names, etc.) are case-sensitive in some systems, but not in other systems. Hence, it is best to treat identifiers as case-sensitive.

SHOW DATABASES

You can use SHOW DATABASES to list all the existing databases in the server.

The databases "mysql", "information_schema" and "performance_schema" are system databases used internally by MySQL. A "test" database is provided during installation for your testing.

Let us begin with a simple example - a product sales database. A product sales database typically consists of many tables, e.g., products, customers, suppliers, orders, payments, employees, among others. Let's call our database "southwind" (inspired from Microsoft's Northwind Trader sample database). We shall begin with the first table called "products" with the following columns (having data types as indicated) and rows:

2.1  Creating and Deleting a Database - CREATE DATABASE and DROP DATABASE

You can create a new database using SQL command "CREATE DATABASE databaseName"; and delete a database using "DROP DATABASE databaseName". You could optionally apply condition "IF EXISTS" or "IF NOT EXISTS" to these commands. For example,

Database: southwind
Table: products productID
INT productCode
CHAR(3) name
VARCHAR(30) quantity
INT price
DECIMAL(10,2) 1001 PEN Pen Red 5000 1.23 1002 PEN Pen Blue 8000 1.25 1003 PEN Pen Black 2000 1.25 1004 PEC Pencil 2B 10000 0.48 1005 PEC Pencil 2H 8000 0.49

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.