Database Systems
MySql
The Database
1. Display Game (Obtained from GameName) and Publisher for every game's publisher who has more than 2 words. (like) 2. Display Name (obtained from CustomerName), Address (obtained from CustomerAddress) and Gender (obtained from CustomerGender) for every customer who does a transaction on Saturday. (join, datename, weekday) 3. Display Game (obtained from GameName), Sold Count (obtained from the number of games sold ended with ' pc(s) ) and Subtotal (obtained from the number of games sold multiply by Price) for every game with odd number GameId. (cast, count, right, join) Display 'Expense' as Type, Name (obtained from CustomerName), Total (Obtained from the average price of game multiplied by quantity started by 'Rp. ' and ended by ',-'), and Age (obtained from the difference between DOB and today) for every male Customer over 19 years old who has bought the game(s) in GAME Store and also display 'Income' as Type, Name (obtained from StaffName), Total (Obtained from the total of quantity sold by the staff multiply 100000) and and Age (obtained from the difference between DOB and today) for every female Staff over 19 years old who has served the customer(s). (cast, avg, datediff, sum, union, getdate) Display Name (obtained from StaffNames started by 'Mrs. '), Date of Birth (obtained from StaftDOB), Address (Obtained from StaffAddress) for every female staff who handled the transaction after 4th October 2016. (left, charindex, in, subquery) Display Name (obtained from CustomerName), Address (obtained from CustomerAddress by removing 'Jalan'), Date of Birth (obtained from CustomerDQB), Gender (obtained from customer gender) for every customer whose name starts with ' R ' and whoever had bought the game with quantity more than or equals average sold quantity. (replace, avg, like, alias subquery) Create view named 'ViewAllCustomerIn2017' to display Date (Obtained from TransactionDate), Initial (obtained from the first 2 characters from CustomerName), Gender (obtained from CustomerGender), Address (obtained from CustomerAddress) for every customer who did transaction in 2017. (create view, left, year) Create view named 'ViewCustomerAverageExpense' to display Customer (obtained from CustomerName) and AverageExpense (obtained from average price multiplied by quantity in decimal form (2 digits after comma)) for every CustomerName that contains 'an'. (create view, cast, avg, like) Add column 'Rating' on Game Table with integer value and also add constraint to check the rating must be more than 0 and less than or equals 5 . (alter table, add, add constraint) 0. Decrease GamePrice by 100000 for every Game purchased in February and its price above 200000. (update, month) Before updated: After updated
