Create a database for the following scenario using SQL Commands, the primary key should be generated by default, and create the class diagram. The system is an integrated program developed to save time and effort. This website provides a quick and easy way to find out what medicines are available in the pharmacy as well as to buy medicine online. There are employees in this system, and they assigned to many roles. Write the SQL Statement for the following: 1. Insert 5 records in each table. 2. Update 2 records in each table. 3. Delete one record from each table. 4. Retrieve all the data from all tables. 5. Retrieve customers whose name contains an 'a' letter. 6. Retrieve employees whose position is Admin or Pharmacist. (Using two ways). 7. Retrieve the employees whose name ends with 'd' and contains five alphabets. 8. Retrieve employees who have the 3 highest salaries. 9. Retrieve the name with the uppercase first letter. 10. Set the salary value 600 JD when dating of employment before 27/10/2021.

Create a database for the following scenario using SQL Commands, the primary key should be generated by default, and create the class diagram. The system is an integrated program developed to save time and effort. This website provides a quick and easy way to find out what medicines are available in the pharmacy as well as to buy medicine online. There are employees in this system, and they assigned to many roles. Write the SQL Statement for the following: 1. Insert 5 records in each table. 2. Update 2 records in each table. 3. Delete one record from each table. 4. Retrieve all the data from all tables. 5. Retrieve customers whose name contains an 'a' letter. 6. Retrieve employees whose position is Admin or Pharmacist. (Using two ways). 7. Retrieve the employees whose name ends with 'd' and contains five alphabets. 8. Retrieve employees who have the 3 highest salaries. 9. Retrieve the name with the uppercase first letter. 10. Set the salary value 600 JD when dating of employment before 27/10/2021.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter3: Creating Tables

Section: Chapter Questions

Problem 13RQ

See similar textbooks

Similar questions

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
Write SQL statements to do the following on the database schema shown below in the picture. a) Insert a new record in the course table with the following data values course number = 'COSC4390' course name = 'Knowledge Engineering' course credit hours= ‘3’ department = ’COSC'>. b) Change the class of student 'Ahmed' to 4. c) Delete the record of the student whose name is 'Ahmed' and his number is 22.
Answer the following questions 1.Explain by using a practical scenario what the difference between a variable and a parameter are. 2. Using a practical scenario explain the difference between Desktop databases and Server Databases. 3. You have been hired as a Database Developer and you have been given the option to use a trigger and a procedure. When would it be best to use procedures? 4. Write a SQL statement that will create a database named Namibian_Team that will store data for all sporting events represented in the country using the Master database in MS SQL Server. 5. As a database developer, you have to explain with visual representations the difference between a logical design and a physical design
Assume that all the statements from Q.6.2 have been executed on the database.Q.6.3.1 Write a SQL statement to get the list of all the team members in thedatabase. Include only the name and email address of the teammembers.
below is the "battleships” database schema: Classes(class, type, country, numGuns, bore, displacement) Ships(name, class, launched) Battles(name, date) Outcomes(ship, battle, result) Write the following as SQL Assertions: 1) No country may have both battleships and battle-cruisers. 2) For every class, there is a ship with the name of that class.
Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:00-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. Express the following queries using appropriate SQL statements with a minimum number of operations: (a) List the name, SSN and number of courses the student has taken and completed, i.e. received a letter grade (courses with the same CourseNumber taken in different quarters are counted as different courses). (b) List the name, SSN and number of courses the student has taken and completed with a letter grade C or better (courses with the same CourseNumber taken in different quarters are counted as different courses). (c) List the Course Title and CourseNumber which does not have any prerequisite. (d) List the name of every student and SSN who…
Write an SQL statement to list the LastName and FirstName of customers who have purchased an item that was created by an artist with a LastName that begins with the letter J. Use a subquery. From Database Fundamentals Ch. 3 (Kroenke)
can you help me iwth these two queries in mySQL. Write a SQL statement to create a table job_history including columns employee_id, start_date, end_date, job_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key column job_id contain only those values which are exists in the jobs table. Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.
Update this database with the following stored modules and submit the updatedCompanyDB.sql file as your solution. Additionally, provide screenshots for all the code 1. A stored procedure that takes in a department identifier and prints a list of all employeesin that department.2. A stored procedure that lists all employee names that do not have any dependents.3. A function that takes in an employee id and returns the number of hours the employee isworking on all their assigned projects4. A stored procedure that updates the ‘Hours’ field for all employees in the employee tableusing a cursor and the function defined in part 3.5. Triggers that are activated after any inserts, deletes, or updates on the WORKS_ON table.The triggers will update the Hours field in the Employee table to reflect any changes forthe hours worked.6. A stored procedure that lists any employees working less than 20 hours.
In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. Here is the table structure: CREATE TABLE USER_ ( USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (USER_ID) ); CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…
In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. Here is the table structure: CREATE TABLE USER_ ( USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (USER_ID) ); CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…
Continuing with the Vince's Vinyl scenario from Hands-On Database: Technically, if there are 20 people in the class, you can come up with 20 different designs and they can still work. To make it easy for the following assignments, can you using a normalized ERD for Vince's Vinyl given? Using the CreateTutorManagement.sql file as a guide, can you create the database in SQL Server using SQL code in SQL Server Management Studio? Can you create the tables in the new database using SQL code, selecting appropriate data types for the columns, setting a primary key for each table, and setting allow nulls as appropriate ?