SQL for data Management

fpuzbir
timer Asked: Oct 26th, 2014

Question Description

Provide select statements to satisfy the following data requests:

  1. List all active male students assigned to Advisors 1 or 3 (Fred Stone or Jack Simpson).
  2. Provide a list of all students without a biography.
  3. What classes are in the English department?
  4. Create a list of all students and their advisors. Sort by the advisor’s name and then the student’s name. Include the student’s birth date, gender, and GPA.
  5. How many students were born in the 1980s?
  6. Write a query to show the average GPA by gender.
  7. Provide a list of all advisors and the number of active students assigned to each. Filter out any advisors with more than 1 student.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step.

20141019104324sql2.docx 

Unformatted Attachment Preview

use University go -- 1 -- we should alter the table classes to accespt description of length 500 ALTER TABLE Classes ALTER COLUMN Description varchar(500) insert into Classes values ('ACCT306', 'Accounting 1', 'This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.') insert into Classes values ('CS362', 'Structured Query Language for Data Management', 'This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.') insert into Classes values ('ENG115', 'Englih Composition', 'In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.') insert into Classes values ('FIN322', 'Investments','This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.') -- 2 insert into Advisors values ('Fred', 'Stone', 'fred@college.edu') insert into Advisors values ('Bob', 'Gordon', 'bob@college.edu') insert into Advisors values ('Jack', 'Simpson', 'jack@college.edu') -- 3 insert into Students values ('Craig', 'Franklin', '1970-03-15', 'M', '2010-05-30', 3.50, 1, '3', null) insert into Students values ('Harriet' , 'Smith', '1982-04-15', 'F', '2010-05-30', 3.22, 1, '1', null) insert into Students values ('George','David', '1984-11-05', 'M', '2010-10-01', 0.00, 1, '3', null) insert into Students values ('Ben', 'Jeffersoon', '1976-09-25', 'M', '2009-02-21', 1.80, 0, 'The student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year.', 3) -- 4 delete from Classes where ClassName ='Investments' -- 5 update Students set BirthDate = '1982-04-25', GPA=3.25 where FirstName = 'Harriet' and LastName='Smith'
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors