2023年11月25日

数据库代写 | assignment 3

本次代写主要为sql查询相关的assignment

Q3
Download sample A4.sql file from given website link and run it in SQLite client.
You will get 10 tables. Some basic scenario is given below.
Based on the database, write 10 SQL queries with 2 simple queries, 4 medium
queries and 4 advanced or complex queries.
Requirements:
Advanced analysis and complex SQL queries (e.g., a query with more than four
joins and more than three conditions in the WHERE clause; most importantly it
must be a meaningful query in real-world scenarios).
Medium queries (e.g., a query with two to four joins with relevant conditions in
the WHERE clause; most importantly it must be a meaningful query in real-world
scenarios).
Simple queries (e.g., a query with one or no joins included; most importantly it
must be a meaningful query in real-world scenarios).
Your queries must be working in SQLite client.

Data Base Info
A player has a unique player ID, other basic info as first name, last name, the age
and gender. A player can have more than one role in the game, or just a new
registered player with no roles created yet.
For each role, it has a unique role ID. A role has a role name and is composed by
some other elements like race (Dwarf, Human, Night elf), level and class (warrior,
mage, rogue…). Each role is bind to a certain server when created.
A Server is the place located each role. A server has unique server ID, a server
name, and its capacity (the max number of roles can be registered).
Class defines class character of each role, in the game, class can be selected as
warrior, mage, rogue, etc. Class has a unique classID, a class name and attack
attribute which elaborates as melee and ranged.
Race defines race character of each role, in the game, race can be selected as
dwarf, human, night elf, etc. Race has a unique raceID, a race name and
raceFeature attribute which represents as strength, agile, stamina, wisdom.
Different race may have same feature, one race can only have one feature.

Dungeon is the place where roles can kill the beasts and attain reward. Each
dungeon has a unique ID within all servers. Each dungeon has a name and level
which explains the difficulty for conquering it.
Achievement is a reward title gained by each role when certain tasks have been
completed. For instance, when a role gets to level 60, it will get an achievement
“Top Level”. Achievement has unique ID, the name of achievement and certain
describe.
Each role can complete certain tasks to get an achievement, different roles created
by the same player can have the same achievement. Which is to say, when a
player creates a role with roleID xxxx1 to complete a task, the player has “taks1-
achievement”. And when the same player creates another role with roleID xxxx2
can finish the same task1, and the new achievement gained by the new role.