Question

Experiment with SQL queries on 2 tables:

Background: Normalized databases will involve data that is organized into 2 or more tables. However, to answer queries, the data from 2 (or more) tables will need to be joined together to get the relevant information. Join operations in SQL are accomplished in the following manner:

1. Indicate the tables involved in the query in the FROM clause
2. Specify relationship/condition between columns in from the tables.

Exercise: For this part of the exercise the following tables have already been created and populated with data for you:

Product (pname, price, category, manufacturer) Company (cname, stockPrice, country)

1. Develop a SQL query (no join needed) to list all rows & columns in Company table (copy-paste your SQL statement and output in the space below):  

2. Develop a SQL query (no join needed) to list all rows & columns in Product table (copy-paste your SQL statement and output in the space below):

3. Develop a SQL query to list all names of products (pname) manufactured by Japanese companies (copy-paste your SQL statement and output in the space below):

4. Develop a SQL query to list names and stock prices of companies that manufacture products cheaper than $200 (copy-paste your SQL statement and output in the space below):

Answer 1

for using JOIN there should be pimarykey in company table which later join with the foreign key of table product

CREATE TABLE `company` (

`cno` varchar(35) NOT NULL primarykey,

  `cname` varchar(35) NOT NULL,

  `stockPrice` int(11) NOT NULL,

  `country` varchar(35) NOT NULL

);

INSERT INTO `company` (`cname`, `stockPrice`, `country`) VALUES

('infosys', 1000, 'India'),

('tcs', 2000, 'India');

CREATE TABLE `product` (

`cno` varchar(35) NOT NULL FOREIGN KEY REFERENCES company(cno),

  `pname` varchar(35) NOT NULL ,

  `price` int(11) NOT NULL,

  `category` varchar(35) NOT NULL,

  `manufacturer` varchar(35) NOT NULL

);

INSERT INTO `product` (`pname`, `price`, `category`, `manufacturer`) VALUES

('abcd', 100, 'xyz', 'klm'),

('abc', 500, 'sdf', 'japanese');

1. list all rows & columns in company table

SELECT * FROM `company` WHERE 1

2. list all rows & columns in product table

SELECT * FROM `product` WHERE 1

3.list all names of products (pname) manufactured by Japanese companies

SELECT * FROM `product` WHERE manufacturer="japanese"

4. list names and stock prices of companies that manufacture products cheaper than $200

SELECT company.cname, company.stockPrice
FROM company
JOIN product ON company.cno=product.cno

WHERE product.price<200;