Lecture Example

CREATE  DATABASE Lec;
USE Lec; 

CREATE TABLE product(
       pname        VARCHAR(20) PRIMARY KEY, -- name of the product
       price        DECIMAL(10,2),               -- price of the product
       category     VARCHAR(20),             -- category
       manufacturer VARCHAR(20) NOT NULL     -- manufacturer
);
INSERT INTO product VALUES('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');    
INSERT INTO product VALUES('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');  
INSERT INTO product VALUES('MultiTouch', 203.99, 'Household', 'Hitachi'); 
INSERT INTO product VALUES('SingleTouch', 149.99, 'Photography', 'Canon')

Exercise 1

Try writing a query to get an output table of all the products with "Touch" in the name, showing just their name and price, and sorted alphabetically by manufacturer.

Next, write a query that returns the distinct names of manufacturers that make products with "Gizmo" in its name

Exercise 2

Try some of these queries but first guess what they return.

SELECT DISTINCT category FROM product ORDER BY category;
SELECT category FROM product ORDER BY pname;
SELECT DISTINCT category FROM product ORDER BY pname;