SQL Queries and Database Management: A Comprehensive Guide
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';