{"id":3090,"date":"2016-05-18T00:00:34","date_gmt":"2016-05-18T00:00:34","guid":{"rendered":"https:\/\/assignment.essayshark.com\/blog\/?p=3090"},"modified":"2023-01-02T09:24:26","modified_gmt":"2023-01-02T09:24:26","slug":"sql-simple-examples-database","status":"publish","type":"post","link":"https:\/\/assignmentshark.com\/blog\/sql-simple-examples-database\/","title":{"rendered":"SQL Simple Examples: Database"},"content":{"rendered":"

MS SQL TASK:<\/h2>\n

Create new MS SQL database. In this database, create a table called \u201clibrary\u201d with the following information:<\/p>\n

Andrew Jacobson (2003 April 27) \u201cBlue Gift\u201d. Publisher: CRC Press\r\nBill Kauffmann (2005 March 6) \u201cWillow in the Mist\u201d. Publisher: Delirium Books\r\nBill Kern (2005 March 1) \u201cTime of Silence\u201d. Publisher: Del Sol Press\r\nCatherine Wilde (2008 June 30) \u201cBlack Tears\u201d. Publisher: CRC Press\r\nJohn Becker (2008 May 2) \u201cSplintered History\u201d. Publisher: ABC Publishing\r\nJohn Becker (2006 July 17) \u201cLost Ashes\u201d. Publisher: Delirium Books\r\nJohn Becker (2010 January 15) \u201cForgotten Girl\u201d. Publisher: ABC Publishing\r\nMichael Ernst (1954 December 4) \u201cTower in the Flame\u201d. Publisher: Beyer Books\r\nNatalie Haast (1988 February 25) \u201cFrozen Legacy\u201d. Publisher: Beyer Books\r\nSamantha Steele (1999 October 8) \u201cDoors of Danger\u201d. Publisher: ABC Publishing\r\nSamantha Steele (1993 February 11) \u201cDiamond Petals\u201d. Publisher: Beyer Books\r\n<\/pre>\n
Execute the following SQL queries and provide the results of their execution in form of screenshots:<\/p>\n
    \n
  1. Display the list of books by authors with their names starting on letters between \u201cC\u201d and \u201cN\u201d<\/li>\n
  2. Display the list of books published between years 1990 and 2004<\/li>\n
  3. Display the list of distinct publishers<\/li>\n
  4. Display the list of books published by ABC Publishing and CRC Press<\/li>\n
  5. Display the list of books published by publishers whose names start from \u201cDel\u201d<\/li>\n<\/ol>\n
    SOLUTION:<\/h2>\n
    First of all, we will create a new database with our first query:<\/p>\n
    CREATE DATABASE Sample\r\nON\r\n(\r\n\tNAME = \"sample_db_logical\",\r\n\tFILENAME = \"D:\\sample .mdf\",\r\n\tSIZE = 20 MB,\r\n\tMAXSIZE = 50 MB,\r\n\tFILEGROWTH = 10 MB\r\n)\r\nLOG ON\r\n(\r\n\tNAME = \"sample_db_log_logical\",\r\n\tFILENAME = \"D:\\sample.ldf\",\r\n\tSIZE = 20 MB,\r\n\tMAXSIZE = 50 MB,\r\n\tFILEGROWTH = 10 MB\r\n)\r\n<\/pre>\n
    \"Create<\/a><\/p>\n
    In order for our next queries to interact with this database, we should mark it as the one currently used:<\/p>\n
    USE Sample<\/pre>\n
    Result:<\/p>\n
    \"Use<\/a><\/p>\n
    Next, we should create a new table called \u201cLibrary\u201d. 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:<\/p>\n
    CREATE TABLE Library\r\n(\r\n\tbook_id int NOT NULL PRIMARY KEY,\r\n\tbook_name char(50) NOT NULL,\r\n\tauthor_name char(50) NOT NULL,\r\n\tpublished_date date NOT NULL,\r\n\tpublisher_name char(50) NOT NULL,\r\n)\r\n<\/pre>\n
    Results:
    \n    \"Create<\/a><\/p>\n
    The rows should be filled with appropriate data:<\/p>\n
    INSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(1,'Blue Gift','Andrew Jacobson','2003-04-27','CRC Press')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(2,'Willow in the Mist','Bill Kauffmann','2005-03-06','Delirium Books')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(3,'Time of Silence','Bill Kern','2005-03-01','Del Sol Press')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(4,'Black Tears','Catherine Wilde','2008-06-30','CRC Press')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(5,'Splintered History','John Becker','2008-05-02','ABC Publishing')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(6,'Lost Ashes','John Becker','2006-07-17','Delirium Books')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(7,'Forgotten Girl','John Becker','2010-01-15','ABC Publishing')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(8,'Tower in the Flame','Michael Ernst','1954-12-04','Beyer Books')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(9,'Frozen Legacy','Natalie Haast','1988-02-25','Beyer Books')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(10,'Doors of Danger','Samantha Steele','1999-10-08','ABC Publishing')\r\nINSERT INTO Library(book_id,book_name,author_name,published_date,publisher_name)\r\nVALUES(11,'Diamond Petals','Samantha Steele','1993-02-11','Beyer Books')\r\n<\/pre>\n
    Results:
    \n    \"Insert<\/a><\/p>\n
    At this point, our table is created and filled with data, which allows us to start working on first out of four required queries, \u201cDisplay the list of books by authors with their names starting on letters between C and N\u201d:<\/p>\n
    SELECT *\r\nFROM Library\r\nWHERE author_name BETWEEN 'C' AND 'N'\r\n<\/pre>\n
    Result:<\/p>\n
    \"Select<\/a><\/p>\n
    As we can see, the current range treates letter \u201cC\u201d as inclusive but letter \u201cN\u201d as exclusive, so we may want to modify the query:<\/p>\n
    SELECT *\r\nFROM Library\r\nWHERE author_name BETWEEN 'C' AND 'O'\r\n<\/pre>\n
    Result:
    \n    \"Select<\/a><\/p>\n
    Second query, \u201cDisplay the list of books published between years 1990 and 2004\u201d, also requires us to set a range<\/p>\n
    SELECT *\r\nFROM Library\r\nWHERE published_date BETWEEN '1990-01-01' AND '2004-12-31'\r\n<\/pre>\n
    Result:<\/p>\n
    \"Select<\/a><\/p>\n
    Third query, \u201cDisplay the list of distinct publishers\u201d, requires us to use the \u201cDISTINCT\u201d command:<\/p>\n
    SELECT DISTINCT publisher_name\r\nFROM Library\r\n<\/pre>\n
    Result:
    \n    \"Select<\/a><\/p>\n
    Fourth query, \u201cDisplay the list of books published by ABC Publishing and CRC Press\u201d:<\/p>\n
    SELECT *\r\nFROM Library\r\nWHERE publisher_name = 'ABC Publishing' OR publisher_name = 'CRC Press'\r\n<\/pre>\n
    Result:<\/p>\n
    \"Select<\/a><\/p>\n
    Final query, \u201cDisplay the list of books published by publishers whose names start from \u201cDel\u201d:<\/p>\n
    SELECT *\r\nFROM Library\r\nWHERE publisher_name LIKE 'Del%'\r\n<\/pre>\n
    Result:<\/p>\n
    \"Select<\/a><\/p>\n
    SQL Assignments Help<\/h2>\n
    You’ve looked through one of our SQL simple examples (in case you missed, here is the previous SQL sample<\/a>). 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<\/a>, from our experts. Just specify your expectations and they will be met within a set deadline term.<\/em><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
    MS SQL TASK: Create new MS SQL database. In this database, create a table called \u201clibrary\u201d with the following information: Andrew Jacobson (2003 April 27) \u201cBlue Gift\u201d. Publisher: CRC Press Bill Kauffmann (2005 March 6) \u201cWillow in the Mist\u201d. Publisher: Delirium Books Bill Kern (2005 March 1) \u201cTime of Silence\u201d. Publisher: Del Sol Press Catherine […]<\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53,35],"tags":[],"class_list":["post-3090","post","type-post","status-publish","format-standard","hentry","category-it","category-samples"],"_links":{"self":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/3090","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/comments?post=3090"}],"version-history":[{"count":14,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/3090\/revisions"}],"predecessor-version":[{"id":13159,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/3090\/revisions\/13159"}],"wp:attachment":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/media?parent=3090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/categories?post=3090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/tags?post=3090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}