{"id":3482,"date":"2016-06-28T00:00:00","date_gmt":"2016-06-28T00:00:00","guid":{"rendered":"https:\/\/assignment.essayshark.com\/blog\/?p=3482"},"modified":"2022-01-12T11:02:58","modified_gmt":"2022-01-12T11:02:58","slug":"sql-code-samples-database","status":"publish","type":"post","link":"https:\/\/assignmentshark.com\/blog\/sql-code-samples-database\/","title":{"rendered":"SQL Code Samples: Database"},"content":{"rendered":"<h2>MS SQL TASK:<\/h2>\n<p>Create new MS SQL database. In this database, create a table called \u201cstore\u201d with the following information:<\/p>\n<pre>Apples provided by Sunshine Ltd: 3.08 (expiration date 2016.07.20)\r\nApples provided by NewFood: 3.88 (expiration date 2016.07.30)\r\nApples provided by FreshGoods: 2.53 (expiration date 2016.06.25)\r\nPotatoes provided by Sunshine Ltd: 1.61 (expiration date 2016.10.01)\r\nPotatoes provided by NewFood: 2.01 (expiration date 2016.12.11)\r\nTomatoes provided by Sunshine Ltd: 3.65 (expiration date 2016.05.20)\r\nTomatoes provided by NewFood: 5.30 (expiration date 2016.05.25)\r\nEggs (pack of 12) provided by Sunshine Ltd: 4.59 (expiration date 2016.06.13)\r\nEggs (pack of 12) provided by FreshGoods: 3.13 (expiration date 2016.06.01)\r\nCarrots provided by Sunshine Ltd: 2.50 (expiration date 2016.08.22)\r\nCarrots provided by FreshGoods: 3.30 (expiration date 2016.07.30)\r\nCarrots provided by NewFood: 2.80 (expiration date 2016.08.10)\r\nOnions provided by NewFood: 1.95 (expiration date 2016.05.20)\r\nOnions provided by FreshGoods: 1.35 (expiration date 2016.05.10)\r\nOnions provided by FreshFoods: 5.25 (expiration date 2016.05.25)\r\nPlastic bags provided by Sunshine Ltd: 0.10 (no expiration date)\r\nPaper bags provided by PaperFactory Ltd: 0.15 (no expiration date)\r\nPaper bags provided by FreshMoods: 0.20 (no expiration date)\r\nHandkerchiefs provided by FreshMoods: 0.30 (no expiration date)\r\n<\/pre>\n<p><!--more--><\/p>\n<p>Execute the following SQL queries and provide the results of their execution in form of screenshots:<\/p>\n<ul>\n<li>Display the list of products priced in ranges between 1.50 and 2.00 &amp; between 3.00 and 5.00<\/li>\n<li>Display the list of products without expiration date<\/li>\n<li>Display the list of products provided by Sunshine Ltd which are priced below 3.00 and have an expiration date<\/li>\n<li>Display the list of products provided by NewFood, FreshGoods, and PaperFactory<\/li>\n<li>Display the list of products provided by FreshMoods and FreshGoods (use a single expression)<\/li>\n<li>Display the list of bags provided by those vendors which are not of limited liability (Ltd.)<\/li>\n<li>Display the list of products provided by non-&#8220;Food&#8221;-named vendors, which are priced above 2.60 and will expire by October<\/li>\n<\/ul>\n<h2>SOLUTION:<\/h2>\n<p>First of all, we will create a new database with our first query:<\/p>\n<pre>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<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image001-3.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3488\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image001-3.jpg\" alt=\"image001\" width=\"584\" height=\"525\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image001-3.jpg 584w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image001-3-300x270.jpg 300w\" sizes=\"auto, (max-width: 584px) 100vw, 584px\" \/><\/a><\/p>\n<p>In order for our next queries to interact with this database, we should mark it as the one currently used:<\/p>\n<pre>USE Sample<\/pre>\n<p>Result:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image002-5.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3490\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image002-5.jpg\" alt=\"image002\" width=\"305\" height=\"153\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image002-5.jpg 305w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image002-5-300x150.jpg 300w\" sizes=\"auto, (max-width: 305px) 100vw, 305px\" \/><\/a><\/p>\n<p>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&#8217;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<pre>CREATE TABLE Store\r\n(\r\n\tbook_id int NOT NULL PRIMARY KEY,\r\n\tproduct_name char(50) NOT NULL,\r\n\tprice int NOT NULL,\r\n\texpiration_date date,\r\n\tvendor_name char(50) NOT NULL,\r\n)\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image003-2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3492\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image003-2.jpg\" alt=\"image003\" width=\"370\" height=\"244\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image003-2.jpg 370w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image003-2-300x198.jpg 300w\" sizes=\"auto, (max-width: 370px) 100vw, 370px\" \/><\/a><\/p>\n<p>The rows should be filled with appropriate data:<\/p>\n<pre>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<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image004-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3494\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image004-1.jpg\" alt=\"image004\" width=\"554\" height=\"816\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image004-1.jpg 554w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image004-1-204x300.jpg 204w\" sizes=\"auto, (max-width: 554px) 100vw, 554px\" \/><\/a><\/p>\n<p>At this point, our table is created and filled with data, which allows us to start working on first out of eight required queries, \u201cDisplay the list of products priced in ranges between 1.50 and 2.00 &amp; between 3.00 and 5.00\u201d<\/p>\n<pre>SELECT *\r\nFROM Store\r\nWHERE (price &gt; 1.50 AND price &lt; 2.00) OR (price &gt; 3.00 AND price &lt; 5.00) \r\n<\/pre>\n<p>Results:<br \/>\n<a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image005-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3496\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image005-1.jpg\" alt=\"image005\" width=\"588\" height=\"330\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image005-1.jpg 588w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image005-1-300x168.jpg 300w\" sizes=\"auto, (max-width: 588px) 100vw, 588px\" \/><\/a><\/p>\n<p>Second query, \u201cDisplay the list of products without expiration date\u201d<\/p>\n<pre>SELECT *\r\nFROM Store\r\nWHERE expiration_date IS NULL\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image006-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3498\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image006-1.jpg\" alt=\"image006\" width=\"440\" height=\"240\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image006-1.jpg 440w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image006-1-300x164.jpg 300w\" sizes=\"auto, (max-width: 440px) 100vw, 440px\" \/><\/a><\/p>\n<p>Third query, \u201cDisplay the list of products provided by Sunshine Ltd which are priced below 3.00 and have an expiration date\u201d<\/p>\n<pre>SELECT *\r\nFROM Store\r\nWHERE vendor_name = 'Sunshine Ltd.' AND price &gt; 2.00 AND expiration_date IS NOT NULL\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image007-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3500\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image007-1.jpg\" alt=\"image007\" width=\"643\" height=\"210\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image007-1.jpg 643w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image007-1-300x98.jpg 300w\" sizes=\"auto, (max-width: 643px) 100vw, 643px\" \/><\/a><\/p>\n<p>Fourth query, \u201cDisplay the list of products provided by NewFood, FreshGoods, and PaperFactory\u201d<\/p>\n<pre>SELECT *\r\nFROM Store\r\nWHERE vendor_name IN ('NewFood','FreshGoods','PaperFactory')\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image008-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3502\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image008-1.jpg\" alt=\"image008\" width=\"474\" height=\"337\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image008-1.jpg 474w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image008-1-300x213.jpg 300w\" sizes=\"auto, (max-width: 474px) 100vw, 474px\" \/><\/a><\/p>\n<p>Fifth query, \u201cDisplay the list of products provided by FreshMoods and FreshGoods (use a single expression)\u201d<\/p>\n<pre>SELECT *\r\nFROM Store\r\nWHERE vendor_name LIKE 'Fresh[g-m]oods'\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image009-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3504\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image009-1.jpg\" alt=\"image009\" width=\"430\" height=\"283\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image009-1.jpg 430w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image009-1-300x197.jpg 300w\" sizes=\"auto, (max-width: 430px) 100vw, 430px\" \/><\/a><\/p>\n<p>Sixth query, \u201cDisplay the list of bags provided by those vendors which are not of limited liability (Ltd.)\u201d<\/p>\n<pre>SELECT *\r\nFROM Store\r\nWHERE (product_name LIKE '%bags') AND (vendor_name NOT LIKE '%Ltd.')\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image010-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3506\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image010-1.jpg\" alt=\"image010\" width=\"535\" height=\"180\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image010-1.jpg 535w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image010-1-300x101.jpg 300w\" sizes=\"auto, (max-width: 535px) 100vw, 535px\" \/><\/a><\/p>\n<p>Seventh query, \u201cDisplay the list of products provided by non-&#8220;Food&#8221;-named vendors, which are priced above 2.60 and will expire by October\u201d<\/p>\n<pre>SELECT *\r\nFROM STORE\r\nWHERE (expiration_date &lt; '2016-10-01') AND (price &gt; 2.60) AND (vendor_name NOT LIKE '%Food%')\r\n<\/pre>\n<p>Results:<\/p>\n<p><a href=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image011-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3508\" style=\"max-width: 100%; height: auto;\" src=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image011-1.jpg\" alt=\"image011\" width=\"643\" height=\"241\" srcset=\"https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image011-1.jpg 643w, https:\/\/assignmentshark.com\/blog\/wp-content\/uploads\/2016\/06\/image011-1-300x112.jpg 300w\" sizes=\"auto, (max-width: 643px) 100vw, 643px\" \/><\/a><\/p>\n<blockquote><p><em>This is one of our SQL code samples published on the blog. In case you face difficulties with projects like this and aren&#8217;t able to complete the assigned homework, we will gladly <a href=\"https:\/\/assignmentshark.com\/\" target=\"_blank\" rel=\"noopener\">help with assignments<\/a>. Just fill in the order form, write your requirements and expectations and wait until the delivery.\u00a0<\/em><\/p>\n<p>You can also find one of <a href=\"https:\/\/assignmentshark.com\/blog\/sql-simple-examples-database\/\" target=\"_blank\" rel=\"noopener noreferrer\">SQL simple examples<\/a> completed by our IT experts.<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>MS SQL TASK: Create new MS SQL database. In this database, create a table called \u201cstore\u201d 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 [&hellip;]<\/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-3482","post","type-post","status-publish","format-standard","hentry","category-it","category-samples"],"_links":{"self":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/3482","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=3482"}],"version-history":[{"count":12,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/3482\/revisions"}],"predecessor-version":[{"id":13053,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/posts\/3482\/revisions\/13053"}],"wp:attachment":[{"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/media?parent=3482"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/categories?post=3482"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmentshark.com\/blog\/wp-json\/wp\/v2\/tags?post=3482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}