MySQL Question

mysql question and need an explanation and answer to help me learn.

Hey, I need help with an assignment, I have attached the assignment, one is solved, one is not.
I need your help to solve the assignment and put everything on the file named ( IT244 solve it here pls )

I have a solved version of the assignment, but I’m not sure if it is 100% correct, so I need your help to have a look at it and do it all over again in your own writing, because the solved one might be plagiarized.
Also, for question 3, please if you can change the relational schema mapping please, so it doesn’t look the same

Requirements: enough for a full grade please
Question One
You are in charge of creating a small database for a store that sells goods so that it can keep track of its customers and their orders.
Write an SQL statement to create a customer table containing the following information:
Every customer has a unique ID, that should not be empty and set as the primary key
The customer’s first and last name that shouldn’t be empty
The phone number that shouldn’t be empty
The email, which could be empty.
The address, which could be empty.
Write a SQL statement to create the table Orders, which will keep track of customer orders, using the following information:
Each order has a unique ID, which shouldn’t be empty and should be set as the primary key.
The customer’s ID and should not be empty and assign it as the foreign key of the customer ID in table customer.
The Items purchased by the customer and shouldn’t be left empty.
The amount of each item, and should not be empty
The price of the items, and should not be empty
Question Two
Answer the following questions by referring to the tables below.
COURSE
SECTION
Retrieve a list of all the courses under the “CS” department, listed alphabetically by course name.
Retrieve the section ID, course name, course number, semester, year, and Instructor of the courses taught by Instructor “Anderson”.
Insert a tuple for a new COURSE with the following information:
The course name is Computer networks, and the course number is CS3480. It belongs to the CS department and has a 4-hours credit.
Question Three
Convert each ER into a relational schema mapping
a)
b)
Question One
You are in charge of creating a small database for a store that sells goods so that it can keep track of its customers and their orders.
Write an SQL statement to create a customer table containing the following information:
Every customer has a unique ID, that should not be empty and set as the primary key
The customer’s first and last name that shouldn’t be empty
The phone number that shouldn’t be empty
The email, which could be empty.
The address, which could be empty.
Write a SQL statement to create the table Orders, which will keep track of customer orders, using the following information:
Each order has a unique ID, which shouldn’t be empty and should be set as the primary key.
The customer’s ID and should not be empty and assign it as the foreign key of the customer ID in table customer.
The Items purchased by the customer and shouldn’t be left empty.
The amount of each item, and should not be empty
The price of the items, and should not be empty

Answer
a)
create table customer (
id integer not null,
first_name varchar(25) not null,
last_name varchar(25) not null,
phone varchar(10) not null,
email varchar(50),
address varchar(100),
primary key(id)
);
b)
create table orders (
id integer not null,
customer_id INT not null,
items varchar(50) not null,
amount integer not null,
price decimal(10,2) not null,
primary key(id),
foreign key(customer_id) references customer(id)
);

Question Two
Answer the following questions by referring to the tables below.
COURSE
SECTION
Retrieve a list of all the courses under the “CS” department, listed alphabetically by course name.
Retrieve the section ID, course name, course number, semester, year, and Instructor of the courses taught by Instructor “Anderson”.
Insert a tuple for a new COURSE with the following information:
The course name is Computer networks, and the course number is CS3480. It belongs to the CS department and has a 4-hours credit.

Answer
a)
SELECT * FROM course WHERE department=’CS’ ORDER BY course_name;
b)
SELECT section_id , course.course_name, course.course_number , semester, year , instructor FROM section
INNER JOIN course on course.course_number = section.course_number where instructor = ‘Anderson’;
c)
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES (‘Computer networks’ , ‘CS3480’ , 4 , ‘CS’);

للطلاب اللي الدكتور قالهم محتاج Output مع انه مش مذكور في متطلبات السؤال
a)
SELECT * FROM course WHERE department=’CS’ ORDER BY course_name;
b)
SELECT section_id , course.course_name, course.course_number , semester, year , instructor FROM section
INNER JOIN course on course.course_number = section.course_number where instructor = ‘Anderson’;
c)
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES (‘Computer networks’ , ‘CS3480’ , 4 , ‘CS’);

الكود الكامل الخاص بالسؤال الثاني مع ملاحظة ان اكواد انشاء الجدول ووضع بيانات غير مطلوبة
CREATE TABLE course (
course_name VARCHAR (255) PRIMARY KEY,
course_number VARCHAR (255) NOT NULL,
credit_hours INT,
department VARCHAR (255) NOT NULL
);
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES
(‘Introduction to computer science’ , ‘CS1310’ , 4 , ‘CS’),
(‘Data structures’ , ‘CS3320’ , 4 , ‘CS’),
(‘Discrete Mathematics’ , ‘MATH2410’ , 3 , ‘MATH’),
(‘Database’ , ‘CS3380’ , 3 , ‘CS’);
CREATE TABLE section (
section_id INT PRIMARY KEY,
course_number VARCHAR (255) NOT NULL,
semester VARCHAR (255) NOT NULL ,
year INT,
instructor VARCHAR (255) NOT NULL
);
INSERT INTO section (section_id , course_number , semester , year , instructor)
VALUES (85, ‘Math2410’ , ‘Fall’ , 07 ,’King’),
(92, ‘CS1310’ , ‘Fall’ , 07 ,’Anderson’),
(102, ‘CS3320’ , ‘Spring’ , 08 ,’Knuth’),
(112, ‘Math2410’ , ‘Fall’ , 08 ,’Chang’),
(119, ‘CS1310’ , ‘Fall’ , 08 ,’Anderson’),
(135, ‘CS3380’ , ‘Fall’ , 08 ,’Stone’);
select “—————A—————“;
SELECT * FROM course WHERE department=’CS’ ORDER BY course_name;
select “—————B—————“;
SELECT section_id , course.course_name, course.course_number , semester, year , instructor FROM section
INNER JOIN course on course.course_number = section.course_number where instructor = ‘Anderson’;
select “—————C—————“;
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES (‘Computer networks’ , ‘CS3480’ , 4 , ‘CS’);
select * from course;
select “——————————“;

Question Three
Convert each ER into a relational schema mapping
a)

b)
Answer
a)
b)

Question One
You are in charge of creating a small database for a store that sells goods so that it can keep track of its customers and their orders.
Write an SQL statement to create a customer table containing the following information:
Every customer has a unique ID, that should not be empty and set as the primary key
The customer’s first and last name that shouldn’t be empty
The phone number that shouldn’t be empty
The email, which could be empty.
The address, which could be empty.
Write a SQL statement to create the table Orders, which will keep track of customer orders, using the following information:
Each order has a unique ID, which shouldn’t be empty and should be set as the primary key.
The customer’s ID and should not be empty and assign it as the foreign key of the customer ID in table customer.
The Items purchased by the customer and shouldn’t be left empty.
The amount of each item, and should not be empty
The price of the items, and should not be empty
Question Two
Answer the following questions by referring to the tables below.
COURSE
SECTION
Retrieve a list of all the courses under the “CS” department, listed alphabetically by course name.
Retrieve the section ID, course name, course number, semester, year, and Instructor of the courses taught by Instructor “Anderson”.
Insert a tuple for a new COURSE with the following information:
The course name is Computer networks, and the course number is CS3480. It belongs to the CS department and has a 4-hours credit.
Question Three
Convert each ER into a relational schema mapping
a)
b)
Question One
You are in charge of creating a small database for a store that sells goods so that it can keep track of its customers and their orders.
Write an SQL statement to create a customer table containing the following information:
Every customer has a unique ID, that should not be empty and set as the primary key
The customer’s first and last name that shouldn’t be empty
The phone number that shouldn’t be empty
The email, which could be empty.
The address, which could be empty.
Write a SQL statement to create the table Orders, which will keep track of customer orders, using the following information:
Each order has a unique ID, which shouldn’t be empty and should be set as the primary key.
The customer’s ID and should not be empty and assign it as the foreign key of the customer ID in table customer.
The Items purchased by the customer and shouldn’t be left empty.
The amount of each item, and should not be empty
The price of the items, and should not be empty

Answer
a)
create table customer (
id integer not null,
first_name varchar(25) not null,
last_name varchar(25) not null,
phone varchar(10) not null,
email varchar(50),
address varchar(100),
primary key(id)
);
b)
create table orders (
id integer not null,
customer_id INT not null,
items varchar(50) not null,
amount integer not null,
price decimal(10,2) not null,
primary key(id),
foreign key(customer_id) references customer(id)
);

Question Two
Answer the following questions by referring to the tables below.
COURSE
SECTION
Retrieve a list of all the courses under the “CS” department, listed alphabetically by course name.
Retrieve the section ID, course name, course number, semester, year, and Instructor of the courses taught by Instructor “Anderson”.
Insert a tuple for a new COURSE with the following information:
The course name is Computer networks, and the course number is CS3480. It belongs to the CS department and has a 4-hours credit.

Answer
a)
SELECT * FROM course WHERE department=’CS’ ORDER BY course_name;
b)
SELECT section_id , course.course_name, course.course_number , semester, year , instructor FROM section
INNER JOIN course on course.course_number = section.course_number where instructor = ‘Anderson’;
c)
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES (‘Computer networks’ , ‘CS3480’ , 4 , ‘CS’);

للطلاب اللي الدكتور قالهم محتاج Output مع انه مش مذكور في متطلبات السؤال
a)
SELECT * FROM course WHERE department=’CS’ ORDER BY course_name;
b)
SELECT section_id , course.course_name, course.course_number , semester, year , instructor FROM section
INNER JOIN course on course.course_number = section.course_number where instructor = ‘Anderson’;
c)
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES (‘Computer networks’ , ‘CS3480’ , 4 , ‘CS’);

الكود الكامل الخاص بالسؤال الثاني مع ملاحظة ان اكواد انشاء الجدول ووضع بيانات غير مطلوبة
CREATE TABLE course (
course_name VARCHAR (255) PRIMARY KEY,
course_number VARCHAR (255) NOT NULL,
credit_hours INT,
department VARCHAR (255) NOT NULL
);
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES
(‘Introduction to computer science’ , ‘CS1310’ , 4 , ‘CS’),
(‘Data structures’ , ‘CS3320’ , 4 , ‘CS’),
(‘Discrete Mathematics’ , ‘MATH2410’ , 3 , ‘MATH’),
(‘Database’ , ‘CS3380’ , 3 , ‘CS’);
CREATE TABLE section (
section_id INT PRIMARY KEY,
course_number VARCHAR (255) NOT NULL,
semester VARCHAR (255) NOT NULL ,
year INT,
instructor VARCHAR (255) NOT NULL
);
INSERT INTO section (section_id , course_number , semester , year , instructor)
VALUES (85, ‘Math2410’ , ‘Fall’ , 07 ,’King’),
(92, ‘CS1310’ , ‘Fall’ , 07 ,’Anderson’),
(102, ‘CS3320’ , ‘Spring’ , 08 ,’Knuth’),
(112, ‘Math2410’ , ‘Fall’ , 08 ,’Chang’),
(119, ‘CS1310’ , ‘Fall’ , 08 ,’Anderson’),
(135, ‘CS3380’ , ‘Fall’ , 08 ,’Stone’);
select “—————A—————“;
SELECT * FROM course WHERE department=’CS’ ORDER BY course_name;
select “—————B—————“;
SELECT section_id , course.course_name, course.course_number , semester, year , instructor FROM section
INNER JOIN course on course.course_number = section.course_number where instructor = ‘Anderson’;
select “—————C—————“;
INSERT INTO course ( course_name , course_number , credit_hours , department)
VALUES (‘Computer networks’ , ‘CS3480’ , 4 , ‘CS’);
select * from course;
select “——————————“;

Question Three
Convert each ER into a relational schema mapping
a)

b)
Answer
a)
b)