If we want relational system in conjunction with time we use sixth normal form. At this moment SQL Server does not supports it directly. Yes, I’m familiar with the concept of sixth normal form (6NF) in database management systems (DBMS). It’s an advanced level of normalization in which every non-trivial join dependency in the table is a logical consequence of the candidate keys. This form is rarely used in practical database design due to its complexity and limited applicability. It’s…

Read More Read More

Aggregate and Scalar functions are in built function for counting and calculations. Aggregate functions operate against a group of values but returns only one value. AVG(column) :- Returns the average value of a column COUNT(column) :- Returns the number of rows (without a NULL value) of a column COUNT(*) :- Returns the number of selected rows MAX(column) :- Returns the highest value of a column MIN(column) :- Returns the lowest value of a column Scalar functions operate against a single…

Read More Read More

SELECT * FROM employee WHERE left_val < 7 and right_val > 8 WHERE ORDER BY left_val ASC; To retrieve ancestors of a given node in a hierarchical structure stored in a database using SQL, you typically use recursive queries, which are supported by some relational database management systems (RDBMS) like PostgreSQL, SQL Server, and Oracle. Assuming you’re using a database system that supports recursive queries, here’s an example SQL query: sql WITH RECURSIVE Ancestors AS ( SELECT id, parent_id, name…

Read More Read More

DML stands for Data Manipulation Statements. They update data values in table. Below are the most important DDL statements:- SELECT – gets data from a database table UPDATE – updates data in a table DELETE – deletes data from a database table INSERT INTO – inserts new data into a database table DDL stands for Data definition Language. They change structure of the database objects like table, index etc. Most important DDL statements are as shown below:- CREATE TABLE –…

Read More Read More

SELECT INTO statement is used mostly to create backups. The below SQL backsup the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of pcdsEmployeeBackup and pcdsEmployee table should be same. SELECT * INTO pcdsEmployeeBackup FROM pcdsEmployee. Certainly! The SELECT INTO statement in a database management system (DBMS) is used to create a new table based on the result set returned by a SELECT query. Here’s how it works: SELECT Query: First, you…

Read More Read More

If you want to find out the number of descendants for a node, all you need is the left_val and right_val of the node for which you want to find the  descendants  count. The formula is No. of descendants = (right_val – left_val -1) /2 So,  for 6 -11 Amanda, (11 – 6 – 1) /2 =  2 descendants for 1-12  Peter, (12 – 1 -1 ) / 2 = 5 descendants. for 3-4   Mary, (4 -3 – 1) / 2 =  0,…

Read More Read More

DISTINCT keyword is used to return only distinct values. Below is syntax:- Column age and Table pcdsEmp SELECT DISTINCT age FROM pcdsEmp To select distinct values from a table in a relational database management system (DBMS), you would typically use the SELECT DISTINCT statement. Here’s the general syntax: sql SELECT DISTINCT column1, column2, … FROM table_name; This query will return only unique values from the specified columns in the table. If you want distinct values from all columns, you can…

Read More Read More

View is a virtual table which is created on the basis of the result set returned by the select statement. CREATE VIEW [MyView] AS SELECT * from pcdsEmployee where LastName = ‘singh’ In order to query the view SELECT * FROM [MyView] A view in a database management system (DBMS) is a virtual table that is based on the result of a SELECT query. It presents data from one or more tables in the database in a structured format, similar…

Read More Read More

LIKE operator is used to match patterns. A “%” sign is used to define the pattern. Below SQL statement will return all words with letter “S” SELECT * FROM pcdsEmployee WHERE EmpName LIKE ‘S%’ Below SQL statement will return all words which end with letter “S” SELECT * FROM pcdsEmployee WHERE EmpName LIKE ‘%S’ Below SQL statement will return all words having letter “S” in between SELECT * FROM pcdsEmployee WHERE EmpName LIKE ‘%S%’   “_” operator (we can read…

Read More Read More

It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database. SQL injection attacks typically are easy to avoid…

Read More Read More