2023年11月25日

数据库代写 | Q3 Advanced analysis and complex SQL queries

本次代写主要为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.