SQL SERVER ADMINISTRATION PART 4: PROJECTS

Explore real-world SQL projects in this introduction to SQL administration part four: projects, practice building databases for library management, online store, hospital, and more, with hands-on T-SQL and collaborative work.

Install SQL Server 2022 developer edition, SQL Server Management Studio, Visual Studio, SSRS and SSIS tools, and adapt Adventureworks for 2019; learn default vs named instances and basic firewall considerations.

Download the Adventureworks 2022 backup, create a works folder on drive C, and restore database in SQL Server to prep for reporting with Visual Studio and SSMS, SSRS, and SSIS.

Install Visual Studio Community edition and enable data storage and processing, then add SSRS and SSIS extensions to create SSRS reports and SSIS packages, reboot when prompted.

Verify ssrs and ssis access by opening visual studio, creating a new ssrs reporting project, connecting to adventureworks 2022, testing the connection, and previewing a sample query.

Design efficient databases with normalization and primary/foreign key relationships, and write queries to manipulate and analyze data through live coding demos.

Design and implement a library management database with authors, publishers, books, users, and loans, using primary and foreign keys, normalization, and T-SQL table creation and joins.

Create and relate library tables for books, authors, publishers, users, and loans, emphasizing primary and foreign keys. Learn to insert data and run joins to reveal who loaned which book.

Build an online database by creating products, customers, orders, and order details tables with primary and foreign keys, then query for most popular products, top customers, and total sales.

Learn cleaner sql insert syntax using insert into products values, avoiding repeated column names. Explore joins across products and orders to compute total sales with group by and having.

Build and analyze a movie database schema in SQL Server, create tables (movies, actors, movie_actors, reviews), and visualize relationships with a database diagram, mastering inner, left, and full outer joins.

Test your knowledge in the first quiz of sql server administration part four, covering creating databases and tables, inserting data, and joins to identify negative balances.

Use the master database to create a database and build tables with primary and foreign keys, then perform inner joins to identify negative balances.

Learn to query across multiple databases on the same server using fully qualified names, join school.students with finance.grants, and find the greatest total grant amount with sum and group by.

Configure a maintenance plan to back up full database backups and transactional log backups with separate schedules, and delete old backups after seven days using a cleanup task.

Learn to set up SQL Server Reporting Services and create a drop down parameter in SSRS reports, enabling distinct values and multi-value filtering for end users.

Develop problem solving as a DBA by resolving the missing SQL Server Native Client 11.0 driver and validating fixes with Visual Studio for SSIS.

Create an SSIS package in Visual Studio to import data from Excel to SQL Server, map columns, and set up a scheduled job that truncates the table before each run.

Set up a SQL Server Agent job to run an SSIS package that imports Excel data into a SQL table, using truncate to prevent duplicates, with a scheduled run.

Assess when to shrink a database, weighing free space and unused space against fragmentation (internal and external), and follow best practices: backups and off-peak downtime.

Learn to assess database size and fragmentation, perform shrink operations, back up before changes, and apply index maintenance: reorganize, rebuild, and update statistics for space and performance.

Automate index maintenance by deploying a stored procedure that analyzes fragmentation and automatically reorganizes or rebuilds indexes via a scheduled job, using thresholds to optimize performance.

Create an after insert or update trigger on the employees table to maintain an audit trail in the audit log, capturing who changed data and when.

Explore the four primary SQL joins—inner, left, right, and full outer—through a hands-on demo with employees and department tables, illustrating matching on IDs and nulls for nonmatches.

This project guides you through creating a beer database with tables: products, city, sales, demographics, and competitors, and writes T-SQL to find city with the highest beer sales by quantity.

Learn how to identify the most popular beer type by sales volume using joins, sum, group by, and order by to compute total quantity sold, the ale.

determine which city has the highest average income and how it relates to beer sales by joining sales, city, and demographics, grouping and ordering by average income descending.

Investigate whether city population correlates with beer sales by joining city and sales tables, aggregating total sales, and analyzing results; conclude no clear correlation.

Compute total revenue by beer type by summing quantity times price using inner joins between sales and products, then group by beer type and order by total revenue.

Complete quiz two in sql administration part four. Build a products database with states, cities, and products; answer four questions using aliases and sum functions.

Learn to write a SQL query listing product names with their city and state using aliases p, c, and s, joining products, cities, and states on city_id and state_id.

Compute the total population per city using sum and group by, applying aliases and underscores for readability, in SQL Server administration part four.

Calculate the per-city product count by joining cities and products, grouping by city name, and applying a having count greater than one to reveal Houston, New York City, and Chicago.

Learn to find the city with the largest population in SQL administration part four by selecting city name and population from cities, ordering by 1 desc, or using top 1.

Build and query a hospital SQL database by creating patient, doctor, and appointment tables, joining data to view May 2024 appointments, and updating a doctor’s specialty with a where clause.

Learn to query a hospital database by retrieving doctors' names and patient counts using the count function, inner and right outer joins, group by, then delete with a where clause.

Build and query the travel database by creating and filling the customer, destination, and bookings tables, then practice intermediate SQL concepts like group by having and ordering by booking date.

Learn to extract the month from booking date and compute total revenue per month for 2024 by joining bookings with destinations and aggregating revenue.

Explore how to use a case statement for conditional logic in SQL to categorize destinations by price into budget, mid-range, and luxury, based on defined price ranges.

Identify customers with incorrect emails using SQL like and not like operators, selecting first name, last name, and email. Filter for emails not containing 'example', as demonstrated with Frank Davis.

This quiz walkthrough guides you to create a school database, build students and courses tables with a foreign key, insert data, and apply case statement with a join and aliases.

Write readable SQL scripts with indentation and uppercase keywords, and translate Adventureworks 2022 data into charts for upper management using Power BI or SSR Reporting.

Prepare for junior SQL database roles with recruiter and HR guidance, resume tips, and common interview questions covering backups, restores, joins, indexing, security, and problem solving.