askndmf,dskmlf,bvdmk,v nmdsvjkmc,dvjkcmsdcvjnkmd

sdjfmkgdshjn

1. Database Systems Understanding of Views

2. SQL Views Views in SQL are a type of  virtual table  that simplifies how users interact with data across one or more tables. Unlike  traditional tables , a view in  SQL  does not store data on disk; instead, it dynamically retrieves data based on a pre-defined query each time it’s accessed. SQL views are particularly useful for managing complex queries, enhancing security, and presenting data in a simplified format.

3. SQL Views Purpose A view in SQL is a saved SQL query that acts as a virtual table. It can fetch data from one or more tables and present it in a customized format, allowing developers to: Simplify Complex Queries: Encapsulate complex joins and conditions into a single object. Enhance Security : Restrict access to specific columns or rows. Present Data Flexibly : Provide tailored data views for different users.

4. SQL Views syntax CREATE VIEW view_name AS SELECT column1, column2….. FROM table_name WHERE condition ; Parameters: view_name : Name for the View table_name : Name of the table condition : Condition to select rows

5. Creating table -- Create StudentDetails table CREATE TABLE StudentDetails ( S_ID INT PRIMARY KEY, NAME VARCHAR(255), ADDRESS VARCHAR(255) ); INSERT INTO StudentDetails (S_ID, NAME, ADDRESS) VALUES (1, 'Harsh', 'Kolkata'), (2, 'Ashish', 'Durgapur'), (3, 'Pratik', 'Delhi'), (4, ' Dhanraj ', 'Bihar'), (5, 'Ram', 'Rajasthan'); -- Create StudentMarks table CREATE TABLE StudentMarks ( ID INT PRIMARY KEY, NAME VARCHAR(255), Marks INT, Age INT ); INSERT INTO StudentMarks (ID, NAME, Marks, Age) VALUES (1, 'Harsh', 90, 19), (2, 'Suresh', 50, 20), (3, 'Pratik', 80, 19), (4, ' Dhanraj ', 95, 21), (5, 'Ram', 85, 18);

6. SQL Views example We will be using these  two SQL tables  for examples. StudentDetails StudentMarks

7. SQL Views example In this example, we will create a View named DetailsView from the table StudentDetails . Query: CREATE VIEW DetailsView AS SELECT NAME, ADDRESS FROM StudentDetails WHERE S_ID < 5; To see the data in the View, we can query the view in the same manner as we query a table. Query: SELECT * FROM DetailsView ;

8. SQL Views example StudentDetails . DetailsView

9. SQL Views example Create a view named StudentNames from the table StudentDetails . ORDER BY NAME Query: To see the data in the View, we can query the view in the same manner as we query a table. Query:

10. SQL Views example Create a view named StudentNames from the table StudentDetails . Query: CREATE VIEW StudentNames AS SELECT S_ID, NAME FROM StudentDetails ORDER BY NAME; To see the data in the View, we can query the view in the same manner as we query a table. Query: SELECT * FROM StudentNames ;

11. Database Systems Understanding of joins

12. Joins SQL joins are the foundation of database management systems, enabling the combination of data from multiple tables based on relationships between columns. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries . SQL JOIN clause is used to query and access data from multiple tables by establishing logical relationships between them. It can access data from multiple tables simultaneously using common key values shared across different tables.

13. 1.Natural join Natural join is an SQL join operation that creates a join on the base of the common columns in the tables. To perform natural join there must be one common attribute(Column) between two tables. Syntax : SELECT * FROM TABLE1 NATURAL JOIN TABLE2; Features of Natural Join It will perform the Cartesian product. It finds consistent tuples and deletes inconsistent tuples . (matching values). Then it deletes the duplicate attributes.

14. employee department SELECT * FROM employee NATURAL JOIN department;

15. employee department SELECT * FROM employee NATURAL JOIN department;

16. SQL Outer Join Outer Join ensures that all rows from one or both tables are included in the result, even if there is no match in the other table. It is particularly useful when you need to show all records from one table, including those that don’t have a match in the other table . Types of Outer Joins LEFT OUTER JOIN (or LEFT JOIN) RIGHT OUTER JOIN (or RIGHT JOIN) FULL OUTER JOIN

17. Need of SQL Outer Join Handling incomplete data – Ensuring all records from one table appear even if there's no match in the other. Dealing with data integrity issues – Retaining important records despite missing relationships. Generating reports – Displaying all relevant data, even without matches. Merging datasets – Combining data from different sources while preserving unmatched records

18. SQL Outer Join Types of Outer Joins LEFT OUTER JOIN (or LEFT JOIN) RIGHT OUTER JOIN (or RIGHT JOIN) FULL OUTER JOIN

19. LEFT OUTER JOIN (or LEFT JOIN) The LEFT OUTER JOIN (referred to as LEFT JOIN) returns all rows from the left table, and the matching rows from the right table . If there is no match, the result will include NULL values for columns from the right table . Syntax SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;

20. SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.PK = TableB.PK;

21. RIGHT OUTER JOIN (or RIGHT JOIN) Right outer join produces a complete set of records from Table B, with the matching records (where available) in Table A . If there is no match, the left side will contain null . Syntax SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;

22. SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.PK = TableB.PK;

23. FULL OUTER JOIN Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available . If there is no match, the missing side will contain null . Syntax SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column;

24. SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.PK = TableB.PK;

25. summary