|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/sql-server-part4-advance-level/
课程评论：没有评论
课程名称：SQL Server 第四部分 - 高级水平 概述：本课程将带您深入了解SQL的高级内容，包括常见面试问题的解答以及DDL（数据定义语言）和DML（数据操作语言）的全面讲解。DDL用于定义和修改数据库、架构及表等结构，其命令包括CREATE、DROP、ALTER和RENAME。DML则用于对表内数据的操作，包括SELECT、INSERT、UPDATE和DELETE。 您将学习以下内容： 1. **数据库和表的结构**：数据库是一个结构化容器，里面存放着所有的架构、表和其他对象。架构类似于数据库内的文件夹，有助于逻辑上组织相关的数据库对象。每个表由行和列组成，用于存储实际的数据记录，必须包括列名、数据类型，以及可选的约束（如PRIMARY KEY、NOT NULL或UNIQUE）。 2. **表的创建与删除**：使用CREATE TABLE命令创建新表，用DROP TABLE永久删除表。需要注意避免在表名或列名中使用空格和保留关键字，并采用一致的命名规范。 3. **项目案例**： - **Update_SQL_CustomerTable_ShowDiscount**：动态更新客户的折扣值。 - 更新客户的订单数量，基于最近的销售或类别。 4. **常见面试问题解决**：涵盖在工作场景中常见的SQL面试问题，例如： - 如何使用子查询或ROW_NUMBER()找出表中第四高的ID。 - 如何检索第N高的ID，并支持动态排名逻辑。 - 使用LAG()或自连接查找连续两次登录的员工。 - 找出至少连续出现三次的客户，展示重复行为。 - 编写查询，统计获得比经理更多奖励的员工。 通过本课程，您将能够掌握高级SQL技能，提升在数据库管理和数据操作方面的能力，特别是在求职面试中表现更为出色。
What will you learn: Frequently asked Interview Questions with solutions + DDL DML SQL with full explanation.SQL is broadly categorized into DDL (Data Definition Language) and DML (Data Manipulation Language).DDL is used to define and modify structures like databases, schemas, and tables.Commands like CREATE, DROP, ALTER, and RENAME are all part of DDL.DML is used for manipulating data inside tables-using SELECT, INSERT, UPDATE, and DELETE.A database is a structured container where all schemas, tables, and other objects are stored.A schema is like a folder inside a database that helps logically organize related database objects.A table is a set of rows and columns designed to store actual data records.Every table must have column names, data types, and optional constraints like PRIMARY KEY, NOT NULL, or UNIQUE.Use CREATE TABLE to build new tables and DROP TABLE to delete them permanently.Avoid using spaces and reserved keywords in table or column names; use consistent naming conventions.One project focused on Update_SQL_CustomerTable_ShowDiscount, updating customers' discount values dynamically.Another project updated customer order quantities based on recent sales or category.We used SP_RENAME to rename columns-ensuring backward compatibility without losing data.We practiced solving real SQL interview questions that appear commonly in job scenarios.How to find the 4th highest ID in a table using subqueries or ROW_NUMBER().How to retrieve the Nth highest ID, customizable using dynamic ranking logic.How to identify employees who entered twice consecutively using LAG() or self-joins.Find customers who appeared at least three times consecutively, showing repeat behaviors.Write queries to count employees who got more incentives than their managers using joins.
