SQL Queries and Database Management: A Comprehensive Guide

Posted on Oct 27, 2024 in Computers

SQL Queries and Database Management

1. Selecting Distinct Names

The following query retrieves a list of distinct names from the DPersonal table:

SELECT DISTINCT Name FROM DPersonal;

2. Deleting a Record

To delete the record for order OP1300 from the OrdenPedido table, first drop the foreign key constraint, then delete the record, and finally, recreate the constraint:

ALTER TABLE Office DROP CONSTRAINT FkPedidoDespacho;
DELETE FROM OrdenPedido WHERE Codigo_OP = 'OP1300';
ALTER TABLE Office ADD CONSTRAINT FkPedidoDespacho FOREIGN KEY (Wanted) REFERENCES OrdenPedido (Codigo_OP);

3. Many-to-Many Relationships

When creating a many-to-many (N:M) relationship using a junction table, consider the following:

  • Include the names of both related tables in the junction table name.
  • Both related tables must have primary keys.
  • The junction table must have a composite primary key formed by the foreign keys referencing the related tables.

4. Linking Tables

To link the Office table with the OrdenPedido table using the FkRelacion foreign key constraint:

ALTER TABLE Office ADD CONSTRAINT FkRelacion FOREIGN KEY (Wanted) REFERENCES OrdenPedido (Codigo_OP);

5. Identifying User Views

User views are characterized by:

  • Being a subset of data used by specific users.
  • Corresponding to formal sources of information and documents.

6. Data Dictionary

A data dictionary consists of:

  • A definition and description of each data element or attribute listed in a view.
  • A detailed description of each table and its attributes.

7. Updating Salaries

The following query updates the salaries of employees who meet specific criteria:

UPDATE Salary SET Gratifico = 20000
WHERE Sliquid <= 200000 AND Age <= 20 AND Gratifico IS NULL;

This query assigns a bonus of $20,000 to employees who do not have a bonus, earn less than or equal to $200,000, and have a liquid salary under 21.

8. Deleting a Column

To delete the Result column from the ProcesoFinal table:

ALTER TABLE ProcesoFinal DROP COLUMN Result;

9. Adding a Column

To add the Assistance column of numeric type to the Classes table:

ALTER TABLE Classes ADD Assistance NUMERIC;

10. Normal Forms

Definitions of the first three normal forms:

  • 1NF: Each field must contain only a single value.
  • 2NF: Must be in 1NF and all non-key fields must depend on the entire primary key.
  • 3NF: Must be in 2NF and all non-key fields must be independent of each other.

11. Removing a Column

To remove the Assistance column from the Classes table:

ALTER TABLE Classes DROP COLUMN Assistance;

12. Adding a Foreign Key Constraint

To add a foreign key constraint to the PruebaFinal column in the Test table, referencing the ID_UltimaOpción column in the Repechage table:

ALTER TABLE Test ADD CONSTRAINT PruebaFinal FOREIGN KEY (ID_UltimaOpción) REFERENCES Repechage (ID_UltimaOpción);

13. Adding a Column

To add the detail field to the Sales table:

ALTER TABLE Sales ADD detail VARCHAR(15);

14. Deleting Data

To delete data from the Seller table where id_vendedor is ‘1-9’:

DELETE FROM Seller WHERE id_vendedor = '1-9';

15. Removing a Foreign Key Constraint and Column

To remove the foreign key constraint and the PruebaFinal column from the Test table:

ALTER TABLE Test DROP CONSTRAINT PruebaFinal;
ALTER TABLE Test DROP COLUMN PruebaFinal;

16. Adding a Column

To add the Month column of varchar type to the Classes table:

ALTER TABLE Classes ADD Month VARCHAR(20);

17. Deleting Data

To delete all employees whose names start with the letter “P” from the Employee table:

DELETE FROM Employee WHERE Name LIKE 'P%';

18. Understanding SQL Unions

The following SQL statement retrieves data from multiple tables using UNION:

SELECT CompanyName, City FROM Suppliers WHERE Country = 'Brazil'
UNION
SELECT CompanyName, City FROM Customers WHERE Country = 'Brazil'
UNION
SELECT Name, City FROM Employees WHERE Region = 'South America';

This query retrieves the company names and cities of all suppliers and customers from Brazil, as well as the names and cities of all employees from South America.

20. Querying Multiple Tables

To list all student data, attorney RUT, and head of race name for those enrolled in kitchen:

SELECT DatosP.Rut, DatosP.Nombre, DatosP.Edad, DatosP.Ciudad, Matricula.Rut, JefesCarreras.Nombre
FROM DatosP
INNER JOIN Matricula ON DatosP.Rut = Matricula.alumno
INNER JOIN JefesCarreras ON Matricula.Carrera = JefesCarreras.Codigo
WHERE JefesCarreras.Codigo = 'C200';