Data Analytics

1. What is the meaning of dropout in Deep Learning? Dropout is a technique that is used to avoid overfitting a model in Deep Learning. If the dropout value is too low, then it will have minimal effect on learning. If it is too high, then the model can under-learn, thereby, causing lower efficiency. 2. What are sets in Tableau? Sets are custom fields that define a subset of data based on some conditions. A set can be based on a computed condition, for example, a set may contain customers with sales over a certain threshold. Computed sets update as your data changes. Alternatively, a set can be based on specific data point in your view. 3. What is the difference between DROP and TRUNCATE commands? DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table. 4. What is slicing in Python? Ans: Slicing is used to access parts of sequences like lists, tuples, and strings. The syntax of slicing is-[start:end:step]. The step can be omitted as well. When we write [start:end] this returns all the elements of the sequence from the start (inclusive) till the end-1 element. If the start or end element is negative i, it means the ith element from the end.

We are 10k+ know before the new year 💪 Here is a special channel where you will find essential Data Analysis Book's 👇👇 https://t.me/learndataanalysis You guys are amazing Thanks for sharing and supporting the channel ❤️❤️

1. How to change a table name in SQL? This is the command to change a table name in SQL: ALTER TABLE table_name RENAME TO new_table_name; We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name. 2. How to use LIKE in SQL? The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator SELECT * FROM employees WHERE first_name like ‘Steven’; With this command, we will be able to extract all the records where the first name is like “Steven”. 3. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures? Yes, SQL server drops all related objects, which exists inside a table like constraints, indexes, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table. 4. Explain SQL Constraints. SQL Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY

Data analysis using excel

🗂The order of operations used in MS Excel while evaluating formulas MS Excel follows a standard math protocol to evaluate a formula. This protocol is called “order of operations” – PEMDAS – ~Parentheses ~Exponents ~Multiplication ~Division ~Addition ~Subtraction MS Excel also applies some customization to handle the formula syntax. The order in which MS Excel performs calculations can affect the return value of the formula. First of all, Excel evaluates any expressions in parentheses. As we have seen in mathematical formulae too, parentheses essentially override the normal order of operations. It prioritizes certain operations. Next, Excel resolves cell references like A1 (cell address). It evaluates range references like A1:A10, making them arrays of values. It also performs range operations like a union (comma) and an intersection (space). Next, Excel performs – -Exponentiation -Negation -% conversions -Multiplication and division -Addition and subtraction -Concatenation -Logical operators

Useful Shortcut Keys

1. What is concurrency control in DBMS? This is a process of managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control: Optimistic approach – Involves versioning Pessimistic approach – Involves locking 2. What is a checkpoint in DBMS and when does it occur? A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash. 3. What are groups in Tableau? A group is a combination of dimension members that make higher level categories. For example, if you are working with a view that shows average test scores by major, you may want to group certain majors together to create major categories. 4. How are nested IF statements used in Excel? The function IF() can be nested when we have multiple conditions to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test. 5. Do you wanna make your career in Data Science & Analytics but don't know how to start ? https://t.me/sqlspecialist/94 Here is a complete roadmap from scratch that will make you technically strong enough to crack any Data Scientist / Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.

1. What are the ways to detect outliers? Outliers are detected using two methods: Box Plot Method: According to this method, the value is considered an outlier if it exceeds or falls below 1.5*IQR (interquartile range), that is, if it lies above the top quartile (Q3) or below the bottom quartile (Q1). Standard Deviation Method: According to this method, an outlier is defined as a value that is greater or lower than the mean ± (3*standard deviation). 2. What is a Recursive Stored Procedure? A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. 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.

1. What are Query and Query language? A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database. Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language. 2. What are Superkey and candidate key? A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records. A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records. 3. What do you mean by buffer pool and mention its benefits? A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server. The following are the benefits of a buffer pool: Increase in I/O performance Reduction in I/O latency Increase in transaction throughput Increase in reading performance 4. What is the difference between Zero and NULL values in SQL? When a field in a column doesn’t have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can cancel be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.

1. Define the term 'Data Wrangling. Data Wrangling is the process wherein raw data is cleaned, structured, and enriched into a desired usable format for better decision making. It involves discovering, structuring, cleaning, enriching, validating, and analyzing data. This process can turn and map out large amounts of data extracted from various sources into a more useful format. 2. What are the best methods for data cleaning? Create a data cleaning plan by understanding where the common errors take place and keep all the communications open. Before working with the data, identify and remove the duplicates. This will lead to an easy and effective data analysis process.Focus on the accuracy of the data. Set cross-field validation, maintain the value types of data, and provide mandatory constraints.Normalize the data at the entry point so that it is less chaotic. You will be able to ensure that all information is standardized, leading to fewer errors on entry. 3. Explain 4 steps to use CTE in sql. All CTE starts with "with" clause. After with you need to define CTE name and the field names. For instance in the below code snippet I have 3 fields Count,Column and Id. The name of CTE is "MyTemp". Once you have defined CTE we need to specify the SQL which will give the result for the CTE. Finally you can use the CTE in your SQL query.

Goldman Sachs is hiring Data Analyst! 👇👇 https://t.me/getjobss/1147 Basic Qualifications Minimum of 1 years of experience in Business Intelligence/Digital Transformation gv Analytical, self-motivated, detail-oriented with strong problem solving skills Ability to work within a high-risk environment and meet challenging deadlines and targets Ability to communicate clearly with end users, development managers and other stakeholders Proficient in prescriptive analytics using tools such as Alteryx Proficient in db query languages such as SQL Proficient in Data Visualization with tools such as Tableau Experience with using multi-dimensional data modeling/data warehousing for data analytics and reporting   Preferred Qualifications Graduate or Undergraduate degree in Computer Science, Statistics, Math, or Engineering Experience in gathering and documenting requirements with full testing traceability Experience in predictive analytics using tools such as Alteryx, Tableau Create and maintain ETL processes using a vast array of tools

1. What is RDBMS? How is it different from DBMS? RDBMS stands for Relational Database Management System that stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. 2.What is ETL in SQL? ETL stands for Extract, Transform and Load. It is a three-step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into the tidy format, which will come in the second phase.Finally, we would have to load this tidy data into tools which would help us to find insights. 3. What is a kernel function in SVM? In the SVM algorithm, a kernel function is a special mathematical function. In simple terms, a kernel function takes data as input and converts it into a required form. This transformation of the data is based on something called a kernel trick, which is what gives the kernel function its name. Using the kernel function, we can transform the data that is not linearly separable (cannot be separated using a straight line) into one that is linearly separable. 4. What do you understand by the F1 score? The F1 score represents the measurement of a model's performance. It is referred to as a weighted average of the precision and recall of a model. The results tending to 1 are considered as the best, and those tending to 0 are the worst. It could be used in classification tests, where true negatives don't matter much.

1. Explain Grant and Revoke in SQL. SQL Grant command is specifically used to provide privileges to database objects for a user. This command also allows users to grant permissions to other users too. Revoke command withdraw user privileges on database objects if any granted. It does operations opposite to the Grant command. When a privilege is revoked from a particular user U, then the privileges granted to all other users by user U will be revoked. 2. What are recursive cte, are they available in MYSQL workbench? A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. Yes they are available in MYSQL workbench. 3. What constraints come under the primary key constraint? The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields). Hence the unique constraint and the not null constraint come under the primary key by default. 4. What is the one to many relationship? One-to-Many relationship in DBMS is a relationship between instances of an entity with more than one instance of another entity.A student can work on more than one project. Student and Project are entities here. An individual student working on 2 projects at a time would be considered as One-to-Many relationship in DBMS.

1. What are the various types of refresh options provided in Power BI? Package refresh - This synchronizes your Power BI Desktop or Excel file between the Power BI service and OneDrive, or SharePoint Online. Model or data refresh - This refreshes the dataset within the Power BI service with data from the original data source. Tile refresh - This updates the cache for tile visuals every 15 minutes on the dashboard once data changes. Visual container refresh - This refreshes the visible container and updates the cached report visuals within a report once the data changes. 2. Explain some date manipulation functions in SQL. Getdate: As its name suggests, the getdate function gives us today’s date. Dateadd: The dateadd function is used for adding a time or date interval to a date.Datediff: The datediff function is used for calculating the difference between two dates based on a given interval. Datename: The datename function can be used for extracting the parts of a date. Year, month, day: The year, month, and day functions allow for decomposing a date. 3. What is CTE in SQL? A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement's execution scope. It is temporary because its result cannot be stored anywhere and will be lost as soon as a query's execution is completed.