CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.
Below Sql Query find the second highest salary SELECT * FROM pcdsEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM pcdsEmployeeSalary b WHERE b.salary>=a.salary)) To find the second highest salary from an employee salary table in a Database Management System (DBMS), you can use a query like this: sql SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); This query first finds the maximum salary in the table using MAX(salary). Then, it finds the maximum salary that is less…
Below SQL selects employees born between ’01/01/1975′ AND ’01/01/1978′ as per mysql SELECT * FROM pcdsEmployee WHERE DOB BETWEEN ‘1975-01-01’ AND ‘2011-09-28’ Sure, the BETWEEN clause in a database management system (DBMS) is used to specify a range to test against. It’s commonly used in SQL queries to filter data within a specified range of values. Here’s how it works: sql SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; column_name: The column you want to test against. table_name:…
SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either ‘rohit’ or ‘Anuradha’ SELECT * FROM pcdsEmployee WHERE name IN (‘Rohit’,’Anuradha’) Also you can specify a not clause with the same. SELECT * FROM pcdsEmployee WHERE age NOT IN (17,16).
Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :- INSERT INTO pcdsEmployee SET name=’rohit’,age=’24’; UPDATE pcdsEmployee SET age=’25’ where name=’rohit’; DELETE FROM pcdsEmployee WHERE name = ‘sonia’; Sure, here’s a brief explanation of Insert, Update, and Delete queries in a database management system (DBMS): Insert Query: An Insert query is used to…
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…
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…
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 –…
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…
In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”. Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy. Sure, I’d be happy to explain Fourth Normal Form (4NF) and Fifth Normal Form (5NF). Fourth Normal Form (4NF): 4NF is a level of database normalization that builds upon the concepts of Third Normal…