DMDD HW-2

pdf

School

Northeastern University *

*We aren’t endorsed by this school

Course

DAMG6210

Subject

Computer Science

Date

Apr 3, 2024

Type

pdf

Pages

Uploaded by MajorLoris4143

DMDD HW-2 Part 1. Solution: 1. The SELECT statement to be created is: SELECT CDName, InStock, OnOrder, Reserved FROM CDs; 2. The SELECT statement to be created is: SELECT CDName, InStock, OnOrder, Reserved, InStock+OnOrder-Reserved AS Total FROM CDs; 3. To filter the rows to only include those with a Department value of "Classical" and an InStock value less than 20, WHERE clause can be used in the SELECT statement, which is: SELECT CDName, InStock, OnOrder, Reserved, InStock+OnOrder-Reserved AS Total FROM CDs WHERE Department=’Classical’ AND InStock<20; 4. The SELECT statement to be created is: SELECT Department, Category, COUNT(*) AS Total FROM CDs GROUP BY Department, Category WITH ROLLUP ; Here, The WITH ROLLUP clause generates subtotal rows that represent the grand totals for each Department and overall total for all departments. 5. The SELECT statement to be created is: SELECT Department, Category, COUNT(*) AS Total FROM CDs GROUP BY Department, Category WITH ROLLUP HAVING Total<3; Here, HAVING clause is used is used here to filter the results of aggregate functions applied to groups of rows, filters groups of rows after they have been grouped by the GROUP BY clause. 6. The SELECT statement to be created is: SELECT CDName FROM CDs ORDER BY CDName DESC;

DMDD HW-2 Part 2. Solution: 1. The SELECT statement to be created is: SELECT ProdName, InStock, CASE ProdName WHEN ‘Apples’ THEN ‘On Sale!’ WHEN ‘Oranges’ THEN ‘Just Arrived!’ ELS E ‘Fresh Crop!’ END As Signage FROM Produce WHERE InStock>=1000 ORDER BY ProdName; 2. The SELECT statement to be created is: SELEC T ProdName, Variety, CAST(InStock AS CHAR) AS InStock_CHAR FROM Produce WHERE InStock>=1000 ORDER BY ProdName; 3. The SELECT statement to be created is: SELECT CONCAT(ProdName, ‘ (‘, Variety, ‘)’) AS ProduceVariety, InStock FROM Produce WHERE InStock>=1000 AND Variety IS NOT NULL ORDER BY ProdName; 4. The SELECT statement to be created is: SELECT UPPER(CONCAT(ProdName, ‘ (‘, Variety, ‘)’)) AS ProduceVariety, InStock FROM Produce WHERE InStock>=1000 AND Variety IS NOT NULL ORDER BY ProdName; 5. The SELECT statement to be created is: SELECT Variety, OnOrder, DateOrdered, ADDDATE(DateOrdered, 4) AS DeliveryDate FROM Produce WHERE ProdName=’Apples’ ORDER BY Variety;

DMDD HW-2 6. The SELECT statement to be created is: SELECT ProdName, SUM(InStock) AS TotalInStock, SUM(OnOrder) AS TotalOrdered FROM Produce GROUP BY ProdName;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

DMDD HW-2 Part 3. Solution: 1. It is important to first determine the specific columns and tables needed to retrieve this information. We get the address here from Member Details table, focusing on the Street, City, State, and ZipCode columns. Given that the MemberId (13) is already known, incorporating a WHERE clause that selects only rows where MemberId equals 13 gives the result, the query is: SELECT Street, City, State, ZipCode FROM MemberDetails WHERE MemberId = 13; Result: Street City State ZipCode Winding Road Big City Mega State 34512 2. First the FirstName and LastName columns from the MemberDetails table should be selected. Next, a WHERE clause is incorporated with a condition that verifies last names beginning with "J." Utilizing the LIKE operator, the query is: SELECT FirstName, LastName FROM MemberDetails WHERE LastName LIKE ‘J%’; Result: FirstName LastName John Jackson John Jones Jenny Jones Jack Johnson 3. After selecting LastName and FirstName columns, from MemberDetails table, WHERE clause can be used DateOfJoining, and to obtain the results in the desired order, ORDER BY clause is used, hence the query is: SELECT LastName, FirstName FROM MemberDetails WHERE DateOfJoining <= ‘2004 -12- 31’ ORDER BY LastName, FirstName; Result: LastName FirstName Gee Steve Smith Katie

DMDD HW-2 4. To get the results, we'll need to combine data from three tables: Attendance, Location, and MemberDetails. We'll achieve this by using INNER JOIN to merge the tables. The link between Attendance and Location tables is through the LocationId field, while the link between Attendance and MemberDetails tables is through the MemberId field. With the table joins, we need to narrow down the results to include only the location "Windy Village, Golden State" and ensure that the member actually attended the meeting, denoted by "Y" in the MemberAttended field. We get the results applying the WHERE clause: SELECT FirstName, LastName FROM Attendance INNER JOIN Location ON Attendance.LocationId = Location.LocationId INNER JOIN MemberDetails ON Attendance.MemberId = MemberDetails.MemberId WHERE Location.City = ‘Windy Village’ AND Location.State = ‘Golden State’ AND Attendance.MemberAttended = ‘Y’; Result: FirstName LastName Katie Smith John Jones Jenny Jones

DMDD HW-2 Part 4. Solution: 1. Part-1 Query: SELECT Category, COUNT(DVDPrice), SUM(DVDPrice * 1.1) FROM Films INNER JOIN Category ON Films.CategoryId = Category.CategoryId WHERE AvailableOnDVD = ‘Y’ GROUP BY Category; Result: Category COUNT(DVDPrice) SUM(DVDPrice*1.1) Historical 3 38.423 Horror 2 20.834 Romance 1 14.289 Sci-fi 1 14.289 Thriller 2 17.578 War 1 14.289 Part-2 1 st – Using “FROM” statement to link 2 tables, i.e., “ Films table” and “Category Table” 2 nd - Using “WHERE” Clause to include Available on DVD and “GROUP BY” clause, grouping by category. 3 rd – Adding all necessary columns: Category + Count of No. of DVD’s p er group + Total price of the DVD (incl. 10% sales tax) Finally adding “HAVING” clause to allow only groups w ith one record to be included, Query: SELECT Category, COUNT (DVDPrice), SUM (DVDPrice * 1.1) FROM Films INNER JOIN Category ON Films .CategoryId = Category.CategoryId WHERE AvailableOnDVD = ‘Y’ GROUP BY Category HAVING COUNT (DVDPrice) = 1;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

DMDD HW-2 RESULT: Category COUNT(DVDPrice) SUM(DVDPrice*1.1) Romance 1 14.289 Sci-fi 1 14.289 War 1 14.289 2. Obtaining essential data from the Films and Category tables, linking them through an INNER JOIN on the CategoryId field. The results are then grouped by the Category column, and for each group, we're retrieving the highest and lowest values using the MAX() and MIN() functions. QUERY: SELECT Category, MAX(Rating) AS “Highest Rated”, MIN(Rating) AS “Lowest Rated” FROM Films INNER JOIN Category ON Films.CategoryId = Category.CategoryId GROUP BY Category; RESULT: Category Highest Rated Lowest Rated Historical 5 3 Horror 2 1 Romance 4 4 Sci-fi 5 1 Thriller 4 1 War 5 2

DMDD HW-2 Part 5. Solution: 1. Flow Control Functions: Query 1. Using IF statement to categorize customers based on their total purchase amount: SELECT CustomerName, IF (TotalPurchaseAmount >= 1000, 'Gold Customer', 'Regular Customer') AS CustomerCategory FROM Customers; The IF statement categorizes customers into different categories based on their total purchase amount. Query 2. Using IFNULL function to handle NULL values in a column: SELECT ProductName, IFNULL(DiscountPrice, Price) AS FinalPrice FROM Products; IFNULL function is used to handle NULL values in the DiscountPrice column. It returns the DiscountPrice if it's not NULL, otherwise, it returns the regular Price. 2. Numeric Functions and operators: QUERY 1. Using mathematical operators to perform calculations: SELECT (10 * 5) + (20 / 4) AS Result; Mathematical operators (* for multiplication, / for division, and + for addition) are used to perform calculations. The query calculates the result of the expression (10 * 5) + (20 / 4). QUERY 2. Using ROUND() function to round a number to a specified number of decimal places: SELECT ROUND (15.678, 2) AS Rounded_Number;

DMDD HW-2 ROUND() function is used to round the number 15.678 to two decimal places. 3. Date and time functions: QUERY 1. Using CURDATE() function to get the current date: SELECT CURDATE() AS Current_Date; CURDATE() function is used to retrieve the current date. QUERY 2. Using DATE_ADD() function to add a specified number of days to a date: SELECT DATE_ADD('2024-02-05', INTERVAL 7 DAY) AS New_Date; DATE_ADD() function adds 7 days to the date '2024-02-05', resulting in a new date. 4. String Functions and operators: QUERY 1. Using CONCAT() function to concatenate two strings: SELECT CONCAT('Hello ', 'World') AS Concatenated_String; CONCAT() function is used to concatenate the strings 'Hello ' and 'World', resulting in 'Hello World'. QUERY 2. Using UPPER() function to convert a string to uppercase: SELECT UPPER('hello') AS Uppercase_String; UPPER() function converts the string 'hello' to uppercase, resulting in 'HELLO'.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

DMDD HW-2 5. Full-text search functions: QUERY 1. Using MATCH() AGAINST() for full-text searching: SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL full-text search'); In this query, we're searching the title and content columns of the articles table for the phrase "MySQL full- text search". QUERY 2. Using BOOLEAN MODE modifier with MATCH() AGAINST() for more precise full-text searching: SELECT * FROM products WHERE MATCH(product_name) AGAINST('+apple -juice' IN BOOLEAN MODE); In this query, we're searching the product_name column of the products table for documents that contain the word "apple" but not "juice". The "+" and "-" symbols are used to indicate whether a word is required or excluded in the search results. 6. Cast Functions: QUERY 1. Using CAST() function to convert a string to a numeric type: SELECT CAST('2024-02-05' AS DATE) AS Date_Value; Using the CAST() function to convert the string '2024-02-05' to a DATE data type. QUERY 2. Using CAST() function to convert a string to a numeric type: SELECT CAST('10' AS UNSIGNED) AS Numeric_Value ; In this query, we're using the CAST() function to convert the string '10' to an unsigned integer.

DMDD HW-2 7. Encryption and compression functions: QUERY 1. Using COMPRESS() function to compress a string: SELECT COMPRESS('Lorem ipsum dolor sit amet, consectetur adipiscing elit.') AS Compressed_Data; Using the COMPRESS() function to compress the string 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.'. QUERY 2. Using UNCOMPRESS() function to decompress a compressed string: SELECT UNCOMPRESS(compressed_column) AS Decompressed_Data FROM compressed_table; using the UNCOMPRESS() function to decompress the values stored in the compressed_column of the compressed_table. 8. Information functions: QUERY 1. Using DATABASE() function to retrieve the current database name: SELECT DATABASE() AS Current_Database; QUERY 2. Using VERSION() function to retrieve the MySQL server version: SELECT VERSION() AS MySQL_Version; 9. Aggregate function: QUERY 1. Using AVG() function to calculate the average value of a column: SELECT AVG (salary) AS Average_Salary FROM employees;

DMDD HW-2 The AVG() function is used to calculate the average salary of all employees in the employees table. QUERY 2. Using COUNT() function to count the number of records in a table: SELECT COUNT (*) AS Total_Records FROM employees; COUNT() function is used to count the total number of records in the table specified by employeesTable. 10. Performance scheme functions: QUERY 1. Using sys.schema_table_statistics() function to get statistics about tables: SELECT * FROM sys.schema_table_statistics; The sys.schema_table_statistics() function is used to retrieve statistics about tables, including row counts, index sizes, and data sizes. QUERY 2. Using sys.schema_object_overview() function to get an overview of schema objects: SELECT * FROM sys.schema_object_overview; The sys.schema_object_overview() function is used to retrieve an overview of schema objects, including their names, types, and sizes. 11. Miscellaneous Functions: QUERY 1. Using SLEEP() function to pause execution for a specified number of seconds: SELECT 'Before Sleep'; SELECT SLEEP (5); SELECT 'After Sleep';

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

DMDD HW-2 In this query, the SLEEP() function is used to pause execution for 5 seconds between the 'Before Sleep' and 'After Sleep' statements. QUERY 2. Using NOW() function to retrieve the current date and time: SELECT NOW () AS Current_DateTime; the NOW() function is used to retrieve the current date and time from the server.

DMDD HW-2

Related Documents