SQL Code Samples: Database

MS SQL TASK:

Create new MS SQL database. In this database, create a table called “store” with the following information:

Apples provided by Sunshine Ltd: 3.08 (expiration date 2016.07.20)
Apples provided by NewFood: 3.88 (expiration date 2016.07.30)
Apples provided by FreshGoods: 2.53 (expiration date 2016.06.25)
Potatoes provided by Sunshine Ltd: 1.61 (expiration date 2016.10.01)
Potatoes provided by NewFood: 2.01 (expiration date 2016.12.11)
Tomatoes provided by Sunshine Ltd: 3.65 (expiration date 2016.05.20)
Tomatoes provided by NewFood: 5.30 (expiration date 2016.05.25)
Eggs (pack of 12) provided by Sunshine Ltd: 4.59 (expiration date 2016.06.13)
Eggs (pack of 12) provided by FreshGoods: 3.13 (expiration date 2016.06.01)
Carrots provided by Sunshine Ltd: 2.50 (expiration date 2016.08.22)
Carrots provided by FreshGoods: 3.30 (expiration date 2016.07.30)
Carrots provided by NewFood: 2.80 (expiration date 2016.08.10)
Onions provided by NewFood: 1.95 (expiration date 2016.05.20)
Onions provided by FreshGoods: 1.35 (expiration date 2016.05.10)
Onions provided by FreshFoods: 5.25 (expiration date 2016.05.25)
Plastic bags provided by Sunshine Ltd: 0.10 (no expiration date)
Paper bags provided by PaperFactory Ltd: 0.15 (no expiration date)
Paper bags provided by FreshMoods: 0.20 (no expiration date)
Handkerchiefs provided by FreshMoods: 0.30 (no expiration date)

Execute the following SQL queries and provide the results of their execution in form of screenshots:

• Display the list of products priced in ranges between 1.50 and 2.00 & between 3.00 and 5.00
• Display the list of products without expiration date
• Display the list of products provided by Sunshine Ltd which are priced below 3.00 and have an expiration date
• Display the list of products provided by NewFood, FreshGoods, and PaperFactory
• Display the list of products provided by FreshMoods and FreshGoods (use a single expression)
• Display the list of bags provided by those vendors which are not of limited liability (Ltd.)
• Display the list of products provided by non-“Food”-named vendors, which are priced above 2.60 and will expire by October

SOLUTION:

First of all, we will create a new database with our first query:

CREATE DATABASE Sample
ON
(
	NAME = "sample_db_logical",
	FILENAME = "D:\sample .mdf",
	SIZE = 20 MB,
	MAXSIZE = 50 MB,
	FILEGROWTH = 10 MB
)
LOG ON
(
	NAME = "sample_db_log_logical",
	FILENAME = "D:\sample.ldf",
	SIZE = 20 MB,
	MAXSIZE = 50 MB,
	FILEGROWTH = 10 MB
)

In order for our next queries to interact with this database, we should mark it as the one currently used:

USE Sample

Result:

Next, we should create a new table called “Library”. The command should also specify the names and types (int for storing digits, char for storing characters/strings) of table’s rows. First row will be used as unique row identifier (id), therefore marked as PRIMARY KEY. Additionally, since we do not plan any of these rows to contain no data, we will mark every of them as NOT NULL. This results in the following query:

CREATE TABLE Store
(
	book_id int NOT NULL PRIMARY KEY,
	product_name char(50) NOT NULL,
	price int NOT NULL,
	expiration_date date,
	vendor_name char(50) NOT NULL,
)

Results:

The rows should be filled with appropriate data:

INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(1,'Blue Gift','Andrew Jacobson','2003-04-27','CRC Press')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(2,'Willow in the Mist','Bill Kauffmann','2005-03-06','Delirium Books')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(3,'Time of Silence','Bill Kern','2005-03-01','Del Sol Press')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(4,'Black Tears','Catherine Wilde','2008-06-30','CRC Press')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(5,'Splintered History','John Becker','2008-05-02','ABC Publishing')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(6,'Lost Ashes','John Becker','2006-07-17','Delirium Books')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(7,'Forgotten Girl','John Becker','2010-01-15','ABC Publishing')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(8,'Tower in the Flame','Michael Ernst','1954-12-04','Beyer Books')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(9,'Frozen Legacy','Natalie Haast','1988-02-25','Beyer Books')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(10,'Doors of Danger','Samantha Steele','1999-10-08','ABC Publishing')
INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)
VALUES(11,'Diamond Petals','Samantha Steele','1993-02-11','Beyer Books')

Results:

At this point, our table is created and filled with data, which allows us to start working on first out of eight required queries, “Display the list of products priced in ranges between 1.50 and 2.00 & between 3.00 and 5.00”

SELECT *
FROM Store
WHERE (price > 1.50 AND price < 2.00) OR (price > 3.00 AND price < 5.00) 

Results:

Second query, “Display the list of products without expiration date”

SELECT *
FROM Store
WHERE expiration_date IS NULL

Results:

Third query, “Display the list of products provided by Sunshine Ltd which are priced below 3.00 and have an expiration date”

SELECT *
FROM Store
WHERE vendor_name = 'Sunshine Ltd.' AND price > 2.00 AND expiration_date IS NOT NULL

Results:

Fourth query, “Display the list of products provided by NewFood, FreshGoods, and PaperFactory”

SELECT *
FROM Store
WHERE vendor_name IN ('NewFood','FreshGoods','PaperFactory')

Results:

Fifth query, “Display the list of products provided by FreshMoods and FreshGoods (use a single expression)”

SELECT *
FROM Store
WHERE vendor_name LIKE 'Fresh[g-m]oods'

Results:

Sixth query, “Display the list of bags provided by those vendors which are not of limited liability (Ltd.)”

SELECT *
FROM Store
WHERE (product_name LIKE '%bags') AND (vendor_name NOT LIKE '%Ltd.')

Results:

Seventh query, “Display the list of products provided by non-“Food”-named vendors, which are priced above 2.60 and will expire by October”

SELECT *
FROM STORE
WHERE (expiration_date < '2016-10-01') AND (price > 2.60) AND (vendor_name NOT LIKE '%Food%')

Results:

This is one of our SQL code samples published on the blog. In case you face difficulties with projects like this and aren’t able to complete the assigned homework, we will gladly help with assignments. Just fill in the order form, write your requirements and expectations and wait until the delivery. 

You can also find one of SQL simple examples completed by our IT experts.