I need help with this assignment, please help me. INFO 2630 - Structured Query L

Question

I need help with this assignment, please help me.

INFO 2630 - Structured Query Language PROJECT 50 points. OPTION A SQL Build a database of your choosing, using MySQL or SQL SERVER. Place all of your commands into one script file named project.sql. . Your project will include a one to two-page summary of your database. Save your summary as summary.doc. Minimum requirements include: *Creating a database *Creating a new user *At least three tables with appropriate keys and constraints (3-5 records in each table is sufficient) *Indexes as appropriate *Multiple-table queries (using joins) (use at least three types of joins) *Subqueries (at least three) *Grouping and ordering (at least two of each) *Updating tables (at least two updates to existing data) *Revisions to a table's structure (at least two) *Functions (one character function, one numeric function, and one date function) *Views (at least two) Turn in the following files: Your script file—project.sql A one to two page summary of your database—summary.doc. Create a ZIP archive containing these files and turn in your ZIP archive.

Explanation / Answer

1. Creating a database - To create a database, type the following command. Replace dbname with the name of the database that you want to create:-

2. Creating a new user - CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

localhost is a hostname which means “this computer,” and MySQL treats this particular hostname specially: when a user with that host logs into MySQL it will attempt to connect to the local server by using a Unix socket file. Thus, localhost is typically used when you plan to connect by SSHing into your server or when you’re running the local mysql client to connect to the local MySQL server.

3. At least three tables with appropriate keys and constraints (3-5 records in each table is sufficient)

create table students (
student_no varchar(10),
surname varchar(20),
forename varchar(20),
constraint student_pk primary key (student_no));

create table modules (
module_code varchar(8),
module_name varchar(20),
constraint module_pk primary key(module_code));

create table marks (
student_no varchar(10),
module_code varchar(8),
mark integer,
constraint mark_fk1 foreign key (student_no) references students(student_no),
constraint module_fk2 foreign key (module_code) references modules(module_code));

4) Multiple-table queries (using joins) (use at least three types of joins)

SELECT students.STUDENT_NO, students.surname FROM STUDENT JOIN marks ON students.student_no = marks.student_no;

SELECT students.STUDENT_NO, students.surname FROM STUDENT INNER JOIN marks ON students.student_no = marks.student_no;

SELECT students.STUDENT_NO, students.surname FROM STUDENT OUTER JOIN marks ON students.student_no = marks.student_no;

Q5) Subqueries (at least three)

SELECT students.STUDENT_NO, students.surname FROM STUDENT WHERE students.student_no in (select student_no from marks);

SELECT modules.module_name FROM modules WHERE modules.module_code in (select module_code from marks);

SELECT students.student_no, modules.module_name, mark FROM student, modules, marks where students.student_no in (select student_no from marks) AND modules.module_code in (select module_code from marks);

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects