SQL Discussion

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.

Room 2 - Members: Rama Alzeer, Mahmoud Rumaneh, Farah Alsoqi, Lin Daaboul, Sara Jouma

Q1.

SQL serves as a query programming language designed for managing Relational Database Management Systems (RDBMS). MySQL, in turn, is an RDBMS that employs SQL for its operations. The primary function of SQL revolves around querying and manipulating database systems. MySQL, leveraging SQL, facilitates the handling, storage, modification, and deletion of data, ensuring organized data storage.

Q2.

A Database Management System (DBMS) is essentially a computerized system for maintaining and organizing data. Users of the system are provided with tools to perform various operations, allowing for the manipulation of data within the database or the management of the database structure. DBMSs are classified based on their data structures or types.

Types:

A. Object-Oriented Database:

• Represents information as objects, combining relational database concepts with object-oriented principles.
• Incorporates concepts like data encapsulation, inheritance, and polymorphism.
• Requires less code and is easy to maintain.

B. Hierarchical Database:

• Data elements have a one-to-many relationship (1: N) organized in a tree structure.
• Hierarchy starts from the root node, connecting child nodes to the parent node.
• Typically used on mainframe platforms.

C. Network Database:

• Maintains one-to-one (1: 1) or many-to-many (N: N) relationships among data elements.
• Organizes data in a graph-like structure, allowing multiple parents for one child record.

Q3.

A primary key in SQL refers to either a single field or a group of fields or columns within a table that serves the crucial function of uniquely identifying each row. In essence, it is a designated column that exclusively accepts distinct values for every individual row.

Q4.

A. Arithmetic SQL Operators:

• Perform arithmetic operations like addition, subtraction, multiplication, division, and modulus.
• Examples include + for addition, - for subtraction, * for multiplication, / for division, and % for modulus.

B. Comparison SQL Operators:

• Check the equality of two expressions.
• Used in the WHERE clause, comparison operators evaluate if one expression is identical to another.
• Examples include = for equal to, > for greater than, < for less than, >= for greater than or equal to, <= for less than or equal to, <> or != for not equal to, !> for not greater than, and !< for not less than.

C. Logical SQL Operators:

• Take two expressions as operands and return TRUE or FALSE as output.
• Useful in complex SQL statements, these operators function similarly to logic gates.
• Examples include ALL, AND, ANY, SOME, LIKE, IN, BETWEEN, NOT, EXISTS, OR, and NULL.

Q5.

Group functions in SQL, particularly the GROUPING function, play a crucial role in managing complex queries involving grouping sets, rollup, and cube operations. This function helps differentiate between NULLs representing the absence of data and those present in super-aggregate results. By returning 1 for super-aggregate rows and 0 for regular grouped rows, the GROUPING function aids in clarifying results. Whether used in the SELECT statement, HAVING clause, or ORDER BY clause, this function requires a column name as an argument and must be paired with a GROUP BY clause. Its significance lies in distinguishing NULLs within super-aggregate rows, contributing to clearer and more comprehensible query outcomes.

Q6.

A relationship refers to the established connections between two or more tables, typically based on shared fields. These connections often involve primary and foreign keys.

There are three fundamental types of relationships:

• One-to-one: Each record in the primary table corresponds to one and only one record in the foreign table.
• One-to-many: Each record in the primary table has one or more associated records in the foreign table.
• Many-to-many: Each record in the primary table is linked to multiple records in the foreign table, and vice versa.

**Room 8

Abdullah Alawad / Hassan AbuGhareeb / hammam AbuShehadeh / Noor Alrai**

0. SQL: Structured Query  is a query programming language that manages RDBMS.
   MySQL is a relational database management system that uses SQL.
   

1. DBMS : Data Base management Systems : system that is used to store and mange data
   Types: -Relational -Network DBMS -Object Oriented DB 
   

2. (INNER) JOIN : Returns records that have matching values in both tables.
   returns a result that includes rows from both left and right tables.

   LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
   You can imagine it with a Venn diagram with two circles, with the resulting table being the green highlighted part which includes
   both the common/overlapping part, and the rest of the left circle.

   RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
   The RIGHT OUTER JOIN selects data from the right table (Table B) and matches this data with the rows from the left table
   (Table A). The RIGHT JOIN returns a result set that includes all rows in the right table

   Self Join: join a table to itself, can be viewed as a join of two copies of the same table.

   Cross Join (also known as a cartesian join): returns all combinations of rows from each table.
   CROSS JOIN joins every row from the first table with every row from the second table and its result comprises all combinations of records
   in two tables.

3. PK: is a field that uniquely identify each record in a table

4. Arithmetic / Comparison / Bitwise / Logical / Compound

5.    COUNT(): Counts the number of rows in a set.
      SUM(): Calculates the sum of numeric values in a set.
      AVG(): Computes the average of numeric values in a set.
      MIN(): Finds the minimum value in a set.
      MAX(): Finds the maximum value in a set.  
      
      The need is to summarize Data in one value
   

6.    Relations the established associations between two or more tables
         One to One:
         One to Many
         Many to Many
   

Gorgees, Dana

What is the difference between SQL and MySQL?

• SQL is a query language, whereas MySQL is a relational database that uses SQL to query a database.
• SQL is commercial, MySQL is open source.

What do you mean by DBMS? What are its different types?

• DBMS is system software for creating and managing databases. A DBMS makes it possible for end users to create, protect, read, update and delete data in a database.
  types
• Relational DBMS (RDBMS): Uses a table-based format. Data is structured in rows and columns, forming relations. Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
• Hierarchical DBMS: Organizes data in a tree-like structure. Data is stored hierarchically with a parent-child relationship.
• Network DBMS: Similar to hierarchical DBMS but allows each record to have multiple parent and child records.
• Object-Oriented DBMS: Stores data in the form of objects, as in object-oriented programming.
• Document-Oriented DBMS: Designed for storing, retrieving, and managing document-oriented information.
• NoSQL DBMS: Designed for handling big data and real-time web applications. Non-relational and typically distributed.

What are the types of joins in SQL? Give an example for each one:

• INNER JOIN: SELECT * FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;
• LEFT JOIN: SELECT * FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;
• RIGHT JOIN: SELECT * FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;
• FULL JOIN: SELECT * FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column;

What is a Primary key?

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• We can connect tables by PRIMARY KEY to make foreign keys.

What are the different operators available in SQL?

• Arithmetic Operators (+, -, *, /, %)
• Bitwise Operators (&, |, ^)
• Comparison Operators (=, <, >,..etc)
• Compound Operators (+=, -=, *=, ...etc)
• Logical Operators (ALL, AND, ANY, ...etc)