• +1-617-874-1011 (US)  
  • +44-117-230-1145 (UK)  
Online Customer Service
Follow Us:

Attributes in entity database Homework Help

1: attributes in each entity

Ans:hospital Table:

FIELD NAME

DATA TYPE

h_code

int(50)

address

Varchar(50)

no_of_beds

Int(50)

customer Table:

FIELD NAME

DATA TYPE

cust_id

Int(50)

cust_name

Varchar(50)

address

Varchar(50)

email_id

Varchar(50)

pets Table:

FIELD NAME

DATA TYPE

registration_no

Varchar(50)

pets_name

Varchar(50)

species

Varchar(50)

birth_date

Varchar(50)

fee_detail Table:

FIELD NAME

DATA TYPE

registration_no

Varchar(50)

receipt_no

Varchar(50)

receipt_date

Varchar(50)

total_fee

Varchar(50)

doctor Table:

FIELD NAME

DATA TYPE

dr_id

Int(50)

dr_name

Varchar(50)

sex

Varchar(50)

dr_birthday

Varchar(50)

dr_address

Varchar(50)

mobile_no

Varchar(50)

email_id

Varchar(50)

specialization

Varchar(50)

degree

Varchar(50)

backup_pets

text

inventory Table:

FIELD NAME

DATA TYPE

inventory_identification_no

Int(50)

name

Varchar(50)

description

Varchar(50)

price

Varchar(50)

quantity

Varchar(50)

safety_stock_level

Varchar(50)

2): relationship between the entities
Ans: hospital.doctor_identification_no = doctor. doctor_identification_no

hospital.customer_identification_no = customer. customer_identification_no

customer.inventory_identification_no =inventory.inventory_identification_no

doctor.customer_identification_no=customer.customer_identification_no

3: Retrieve the names and IDs of all faculty members, arranged in alphabetical order by name :

Ans: Select a. FacultyName, a. FacultyID from Faculty a

Order by a.FacultyName

4: Retrieve the IDs and Names of all students taking ART103A

Ans: Select a.StudentID, a.StudentName from student a, Enrolment b
Where a. StudentID = b.StudentID
And b.CourseNumber = 'ART103A'

4: Retrieve the number of students that are taking MTH103C :

Ans: Select count (a.StudentID,) from student a, Enrolment b
Where a. StudentID = b.StudentID
And b.CourseNumber = 'MTH103C'

5: Retrieve the number of students in each course :

Ans: Select b. CourseNumber, count (a.StudentID) “Number of student “ from student a, Enrolment b
Where a. StudentID = b.StudentID
group by b. CourseNumber

6: Change the major of S1013 from Math to Null :

Ans: update Student

set Major = NULL

where StudentID = ‘S1013’

7: New record to the faculty table is:

Ans: INSERT INTO ‘faculty’ ( ‘FacultyID’, ’FacultyName’, ’Department’, ’Rank’ )

VALUES( ‘F330’, ‘Jones’, ‘CSC’, ‘Instructor’ );

STUDENT table:

Student_Name

Student_Number

Grade

Mark

936

13

Spencer

474

81

George

821

27

Mary

748

54

1: SQL statements for “The complete Student table”:

Ans: SELECT * FROM STUDENT

2: Spencer’s details :

Ans: SELECT * FROM STUDENT a

Where a. Student_Name = Spencer’

3: The number of students:

Ans: SELECT count (Student_Number) Number of student FROM student

4: The number of students with a grade of greater than 50:

Ans: select count (Student_Number) from student where Grade>50;

5: The details of students with a grade of greater than 50:

Ans: select * from student where Grade>50;

  1. Display all the rows and columns in the CLIENT table. Sort by client name in reverse alphabetical order.

SELECT*FROM "PartB"."dbo"."client" ORDER BY name DESC;

  1. Display the item number and total cost for each order line (total cost = no of items X item cost). Name the calculated column TOTAL COST.
  1. Display all the client numbers in the ORDER table. Remove duplicates.
  1. Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
  2. Display full details from the ORDER_LINE table where the item number is (first condition) between 1 and 200 (no > or < operators) OR the item number is greater than 1000 AND (second condition) the item cost is not in the list 1000, 2000, 3000 OR the order number is not equal to 1000.
  3. Display the client name and order date for all orders using the traditional method.
  4. Repeat query (7) but also display all clients who have never ordered anything.
  5. Display the client name and order date for all orders using the natural join keywords.
  6. Display the client name and order date for all orders using the JOIN . . . USING method.
  7. Display the client number, order date and shipping date for all orders where the shipping date is between three and six months after the order date.
  8. Display the client number and name and the client number and name of the person who referred that client.
  9. Display the client name in upper case only and in lower case only.
  10. Display the second to fifth characters in each client name.
  11. Display the item_cost and then round it to the nearest hundred, ten, unit, tenth and hundredth.
  12. Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.
  13. Display the order number, order line number and the shipping date. If the shipping date is null, display the string <not shipped yet>.
  14. Display the order number and average item cost for each order.
  15. Display the clientno and total value for all orders placed by that client. Output the result in the following format: Client <clientno> has placed orders to the value of <total value>
  16. Display all clients whose name begins with the letter J or contains the letter M anywhere or contains E as the third letter.
  17. Using a set operator, display the client number of all clients who have never placed an order.
  18. Using a set operator, display the client number of all clients who have ever placed an order and whose name does not contain the string Sm.
  19. Display the order number, number of lines in the order, total number of items and total value for all orders that have a total value greater than $100
  20. Display the client name for all clients who have placed an order where any order line has more than 3 items. Do not use a table join anywhere in your query.
  21. Display the order number for all orders whose average item cost is greater than the overall average item cost across all orders.
  22. Display the client number and the value of the highest value order placed by that client.
  23. Display the earliest shipping date in the format: DD/MON/YYYY
Keywords: Data Relations homework help, SQL homework help, SQL project Help, Oracle Homework Help, XML homework help, XML and Relational Algebra assignment help, SQL Queries and Updates homework assignment help, XML Queries and Transformations tutoring help, Relational Database Design concept, Higher-Level Design help in UML and ERD, Data Warehousing and Data Mining, help with SQL 2008 database, support for Database help, Microsoft access and mysql database programmer, database development and xml database support, homework helper for asp database and uml modeling, uml sequence diagram and uml class diagram, database design development and software SQL server 2008.
Tap to Chat
Get Instant Assignment Help
Tap to Chat
Get Instant Assignment Help