SQL Simple Examples: Database

MS SQL TASK:

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

Andrew Jacobson (2003 April 27) “Blue Gift”. Publisher: CRC Press
Bill Kauffmann (2005 March 6) “Willow in the Mist”. Publisher: Delirium Books
Bill Kern (2005 March 1) “Time of Silence”. Publisher: Del Sol Press
Catherine Wilde (2008 June 30) “Black Tears”. Publisher: CRC Press
John Becker (2008 May 2) “Splintered History”. Publisher: ABC Publishing
John Becker (2006 July 17) “Lost Ashes”. Publisher: Delirium Books
John Becker (2010 January 15) “Forgotten Girl”. Publisher: ABC Publishing
Michael Ernst (1954 December 4) “Tower in the Flame”. Publisher: Beyer Books
Natalie Haast (1988 February 25) “Frozen Legacy”. Publisher: Beyer Books
Samantha Steele (1999 October 8) “Doors of Danger”. Publisher: ABC Publishing
Samantha Steele (1993 February 11) “Diamond Petals”. Publisher: Beyer Books

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

1. Display the list of books by authors with their names starting on letters between “C” and “N”
2. Display the list of books published between years 1990 and 2004
3. Display the list of distinct publishers
4. Display the list of books published by ABC Publishing and CRC Press
5. Display the list of books published by publishers whose names start from “Del”

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 Library
(
	book_id int NOT NULL PRIMARY KEY,
	book_name char(50) NOT NULL,
	author_name char(50) NOT NULL,
	published_date date NOT NULL,
	publisher_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 four required queries, “Display the list of books by authors with their names starting on letters between C and N”:

SELECT *
FROM Library
WHERE author_name BETWEEN 'C' AND 'N'

Result:

As we can see, the current range treates letter “C” as inclusive but letter “N” as exclusive, so we may want to modify the query:

SELECT *
FROM Library
WHERE author_name BETWEEN 'C' AND 'O'

Result:

Second query, “Display the list of books published between years 1990 and 2004”, also requires us to set a range

SELECT *
FROM Library
WHERE published_date BETWEEN '1990-01-01' AND '2004-12-31'

Result:

Third query, “Display the list of distinct publishers”, requires us to use the “DISTINCT” command:

SELECT DISTINCT publisher_name
FROM Library

Result:

Fourth query, “Display the list of books published by ABC Publishing and CRC Press”:

SELECT *
FROM Library
WHERE publisher_name = 'ABC Publishing' OR publisher_name = 'CRC Press'

Result:

Final query, “Display the list of books published by publishers whose names start from “Del”:

SELECT *
FROM Library
WHERE publisher_name LIKE 'Del%'

Result:

SQL Assignments Help

You’ve looked through one of our SQL simple examples (in case you missed, here is the previous SQL sample). It was completed by one of our experts in accordance with our requirements. You can use this sample as a source of ideas for your project, or order any of technical assignments, including SQL assignment help, from our experts. Just specify your expectations and they will be met within a set deadline term.