sql.md

SQL discussions

0. What is the difference between SQL and MySQL?
1. What do you mean by DBMS? What are its different types?
2. What are the types of joins in SQL? Give an example for each one.
3. What is a Primary key?
4. What are the different operators available in SQL?
5. What is the need for group functions in SQL?
6. What is a Relationship and what are they?

Please discuss these questions with your partners and post your answers as a comment on this gist when you are finished.

Nokulunga
Sharon

0. MySQL is the underlying technology(software) that stores the data while SQL(database language) is the language you use to read, modify and delete data.

1. DBMS-A Database Management System (DBMS) is software that provides an interface for users and applications to interact with databases, facilitating the processes of data storage, retrieval, management, and manipulation.
   Types - Relational Database Management System(RDBMS)
   Hierarchical Database Management System (HDBMS)
   Network Database Management System (NDBMS)
   Object-Oriented Database Management System (OODBMS)
   Document-Oriented Database Management System (Document Store)
   Columnar Database Management System (Column Store)

2. (INNER) JOIN : Returns records that have matching values in both tables.
   EXAMPLE: An INNER JOIN will return records where there is a match in both tables. In this case, it will match the CustomerID from the Customers table with the CustomerID from the Orders table.

LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table.
EXAMPLE: A LEFT JOIN will return all records from the Customers table, and the matched records from the Orders table. If there is no match, the result is NULL on the side of the Orders table.

RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
EXAMPLE: A RIGHT JOIN will return all records from the Orders table, and the matched records from the Customers table. If there is no match, the result is NULL on the side of the Customers table.

Full (outer) join returns rows with a match in either of the two tables
EXAMPLE : A FULL JOIN will return all records when there is a match in either the Customers or Orders table. Records from both tables that do not have matching entries in the other table will still be included, with NULLs in place where data is missing from one of the tables.

3. A primary key, also called a primary keyword, is a column in a relational database table that's distinctive for each record.
   EXAMPLE: StudentId

4. Arithmetic Operators
   addition, subtraction, multiplication, division and modulus

Comparison Operators
equal to( =), not equal to (!=), less than (<), greater than (>)

Logical Operators
AND, OR and NOT

Bitwise Operators
AND (&), OR (|), XOR (^), NOT (~), Shift Left (<<) and Shift Right (>>).

Set Operators
UNION: Combines the results of two queries and removes duplicates
UNION ALL: Combines the results of two queries and includes duplicates
INTERSECT: Returns the common records between two queries
EXCEPT (or MINUS in some databases): Returns the records from the first query that are not in the second query.)

5. Group functions help to summarize large amounts of data

   They enable data analysis by allowing you to compute statistical measures such as averages, counts, and variances.

   By using group functions, you can identify patterns and trends within your data. For instance, you can find out which products are the most
   popular by counting the number of times each product has been sold.

   Group functions are often used in conjunction with the GROUP BY clause to perform calculations on subsets of data. This is useful when
   you want to calculate summary statistics for different groups within a dataset, such as total sales per region or average salary per
   department.

   Group functions allow you to filter groups of data using the HAVING clause, which is similar to the WHERE clause but is used for groups of
   data rather than individual rows.

   Using group functions can significantly improve the efficiency and performance of your queries by reducing the amount of data you need
   to process and return. Instead of retrieving and handling large volumes of raw data, you can retrieve summarized data directly from the
   database.

COUNT(): Returns the number of rows.
SUM(): Returns the sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MIN(): Returns the minimum value.
MAX(): Returns the maximum value.

6. In the context of relational databases, a relationship is a connection or association between two or more tables. Relationships are established based on common columns (keys) that link the tables together. Understanding and defining relationships are crucial for structuring data efficiently and ensuring data integrity.

One-to-One (1:1) Relationship
One-to-Many (1
) Relationship
Many-to-Many (M
) Relationship

Lentsoana Hophney
Wesly
Nonhlanhla Mazibuku

0. SQL is a standardized programming language used for managing and manipulating relational databases.
   ----MySQL is a relational database management system that uses SQL as its query language
1. (Database Management System) is software that enables the creation, management, and use of databases.
   Types -----
   Hierarchical DBMS :Data is organized in a tree-like structure.
   Network DBMS :Data is organized in a graph, allowing multiple parent-child relationships.
   columns.
   Object-oriented DBMS (OODBMS):Data is represented as objects, similar to object-oriented programming.
2. Types of joins in SQL
   a)Inner join: Returns rows that have matching values in both tables.
   b)full- outer join: returns all rows when there is a match in one of the tables and NULL values for columns of the table that does not have a match.
   c)Left-outer join: Returns all rows from left table and matching rows from the right table.
   d)Right- outer join: Returns rows from right table and matching rows from the left table.
   e)cross join: Returns all possible rows combinations from the two tables
   f)self-join: regular join but the table is joining by itself.
   g)Natural join: joins based on
3. A Primary Key is a unique identifier for a record in a database table.
4. a)Arithmetic Operators
   b)Comparison Operators
   c)Logical Operators
   d)String Operators
5. group functions are essential for performing calculations on multiple rows of data to produce a single result. They allow you to summarize large datasets and aggregate datasets using some criteria to uncover insights.6. a relationship refers to the connection between tables that defines how the data in one table is related to the data in another.
6. a relationship refers to the connection between tables that defines how the data in one table is related to the data in another.
   One-to-One Relationship, One-to-Many Relationship, Many-to-Many relationship.