Data Analytics

SQL Notes Part-2 - Window Functions

SQL Notes Part-1 Like if you need more content on SQL 😄❤️

Road map to learn SQL https://t.me/sqlanalyst/3

Nice to see amazing response for SQL, Power BI, Statistics, Python & Excel :)

🔰 Learning Python for Data Analysis and Visualization ⏱ 21 Hours 📦 110 Lessons Learn python and how to use it to analyze,visualize and present data. Includes tons of sample code and hours of video! Taught By: Jose Portilla Download Full Course: https://t.me/pythonanalyst/26 Download All Courses: https://t.me/DataAnalystInterview

Additional content posted in this channel for Data Analysis with Python 👇👇 https://t.me/pythonanalyst Interested people can join 👍👍

Data analysis with Python Important Topics 😄❤️

Data Analyst Roadmap 👆👆 More quality content coming soon 😄❤️

1.Define RDBMS. Answer: Relational Database Management System(RDBMS) is based on a relational model of data that is stored in databases in separate tables and they are related to the use of a common column. Data can be accessed easily from the relational database using Structured Query Language (SQL). 2.Define DML Compiler. Answer: DML compiler translates DML statements in a query language into a low-level instruction and the generated instruction can be understood by Query Evaluation Engine. 3.Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of database. Answer: Record: Record is a collection of values or fields of a specific entity. For Example, An employee, Salary account, etc. Field: A field refers to an area within a record that is reserved for specific data. For Example, Employee ID. Table: Table is the collection of records of specific types. For Example, the Employee table is a collection of records related to all the employees. 4.Define the relationship between ‘View’ and ‘Data Independence’. Answer: View is a virtual table that does not have its data on its own rather the data is defined from one or more underlying base tables. Views account for logical data independence as the growth and restructuring of base tables are not reflected in views.

Do you enjoy reading this channel? Perhaps you have thought about placing ads on it? To do this, follow three simple steps: 1) Sign up: https://telega.io/c/sqlspecialist 2) Top up the balance in a convenient way 3) Create an advertising post If the topic of your post fits our channel, we will publish it with pleasure.

1. What is the difference between the RANK() and DENSE_RANK() functions? The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5. 2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset? One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0. 3. What is the shortcut to add a filter to a table in EXCEL? The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L. 4. What is DAX in Power BI? DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have. 5. Define shelves and sets in Tableau? Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data. Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.

Important Topics which you should know to learn SQL 👉Beginners Topics *DDL - Data Definition Language (Create Table,Drop Table,Truncate Table,Alter Table) *DML - Data Manipulation Language (Delete,Update,Insert into) *DQL vs TQL *Constraints (Check,Default,NOT NULL, UNIQUE, Primary key,and Foreign key) *Create and Drop DataBase * SQL Syntax ( SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, LIKE, CASE WHEN, SELECT DISTINCT, AND, OR, NOT, SELECT TOP, LIMIT) * Joins (Inner, Full, Left, Right, Self) & Union * Insert into - Values *Wildcards (%,_,[],^,-,#) *Aggregate functions (Min, Max, Sum,Avg, Count) * Other operators (IN, BETWEEN, ANY, AS, ALL, IFNULL, IsNull) *Data Types(String,DateTime,Number,Boolean) 👉Intermediate Topics *Functions (LEFT,RIGHT,LENGTH,TRIM,POSITION,STRPOS, SUBSTR, CONCAT, UPPER, LOWER, COALESCE) * Sub-queries * Views, Index and Comments * Window Functions (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), OVER, PARTITION BY, Alias) *Formatting Dates in SQL *Pivoting Data in SQL *Tuning SQL queries *Star Schema *Stored Procedures 👉👉 Free Useful Resources to start learning SQL https://t.me/programming_guide/287 https://www.codecademy.com/learn/learn-sql https://www.w3schools.com/sql/ https://www.hackerrank.com/domains/sql https://www.windowfunctions.com/ https://leetcode.com/problemset/database/ https://books.goalkicker.com/SQLBook/SQLNotesForProfessionals.pdf https://imp.i115008.net/WDvrQ3 https://t.me/sqlspecialist/45 https://mode.com/sql-tutorial/introduction-to-sql ENJOY LEARNING 👍👍