Databases

---

Custom Procedures

• Project Specifications
• Personal Queries
• Presentation
• Develop problems that can be given to a developer and create solutions.
• Highlight(s)

/*Q10. Show Products that are no longer in stock (Quantity Zero)*/
USE AdventureWorks2017;
SELECT PP.ProductID,
       PP.Name,
       PINV.Quantity
FROM Production.[Product] AS PP
    LEFT OUTER JOIN Production.[ProductInventory] AS PINV
        ON PP.ProductID = PINV.ProductID
GROUP BY PP.ProductID,
         PP.Name,
         PINV.Quantity
HAVING SUM(PINV.Quantity) = 0;

---

Star Schema Recreation

• Project Specifications
• Personal Stored Procedures: Load_DimProductCategory | Load_DimProductSubCategory
• Presentations: JDBC | System Lifecycle & SSMS
• Recreate the BIClass Database Star Schema using the Original Data Table. Modify all primary keys to eliminate the identity key and use sequence objects in thier place.

---

School Database Designing

• Project Specifications
• Personal Stored Procedure: Department.Instructor
• Personal Queries: Multi_Department_Insturctors | %Instructors_Per_Department
• Design and Create a new Databse (QueensClassScheduleCurrentSemester) from the single table (CoursesCurrentSemester). Create and Document stored procedures to load each of the individual tables based on your databse design.
• Highlight(s)

/*Q1: Shows Instructors that teach in Multiiple Departments*/
SELECT DISTINCT Multi_Department_Instructors.InstructorFullName, 
MAX(Multi_Department_Instructors.Quantity) AS Num_Departments
FROM (
	SELECT DISTINCT InstructorFullName, 
	DENSE_RANK() OVER
		(PARTITION BY InstructorFullName ORDER BY DepartmentName) AS Quantity
	FROM Department.[Instructor]
	GROUP BY InstructorFullName,
			 DepartmentName
	HAVING InstructorFullName <> 'TBA') AS Multi_Department_Instructors
GROUP BY Multi_Department_Instructors.InstructorFullName
HAVING MAX(Multi_Department_Instructors.Quantity) > 1
ORDER BY Num_Departments DESC

/*Q2: Shows the Amount & Percentage of Professors Teaching in Each Department*/
SELECT DISTINCT DepartmentName, COUNT(InstructorFullName) AS Num_Intstructors, 
CONCAT( CAST(COUNT(InstructorFullName) * 100.00/ 
	(SELECT COUNT(InstructorFullName) FROM Department.Instructor) AS DECIMAL (5,2) ), '%') 
	AS Pct_Of_Instructors

FROM Department.[Instructor]
GROUP BY DepartmentName
ORDER BY Num_Intstructors DESC

---

Course Overview:

• Syllabus
• Develop SQL Querying solutions through critical thinking skills in a professional work environment as a team member or leader
• Understanding how to write relational calculi expressions based upon your TSQL querying knowledge
• Understanding and working with metadata and taxonomies
• Creating indexes and foreign keys on your physical database design
• Create ERD diagrams of logical models
• Understanding subject areas in ERD diagrams and how to create them within the SSMS database environment
• Importing and exporting data to/from the database
• Creating reports using SSRS/ReportBuilder 2016

---