Scribd
Upload
2 views

SENTENCIAS_BASICAS_SQL

The document outlines SQL commands for creating a database and tables for students, courses, and instructors, including their attributes and relationships. It includes various SQL operations such as inserting, selecting, updating, and deleting records, as well as aggregate functions and views. Additionally, it demonstrates the use of Common Table Expressions (CTEs) to calculate average salaries by department.

Uploaded by

bacike6619
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views

SENTENCIAS_BASICAS_SQL

The document outlines SQL commands for creating a database and tables for students, courses, and instructors, including their attributes and relationships. It includes various SQL operations such as inserting, selecting, updating, and deleting records, as well as aggregate functions and views. Additionally, it demonstrates the use of Common Table Expressions (CTEs) to calculate average salaries by department.

Uploaded by

bacike6619
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

1.

CREATE DATABASE NAME


2. CREATE TABLE TABLENAME (
STUDENTID INT PRIMARY KEY,
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50),
AGE INT,
EMAIL VARCHAR(100),
LOADDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATEDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. CREATE TABLE COURSES (


COURSEID INT PRIMARY KEY,
COURSENAME VARCHAR(100),
DESCRIPTION TEXT,
INSTRUCTORID INT,
DURATIONINHOURS INT,
LOADDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATEDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (INSTRUCTORID) REFERENCES
INSTRUCTORS(INSTRUCTORID)
);

4. INSERT INTO STUDENTS (FIRSTNAME, LASTNAME, AGE, EMAIL)


VALUES (‘Jhon’, ‘Doe’, 25, ‘jhondoe@example.com’);

5. SELECT * FROM STUDENTS

6. SELECT coursename, description


FROM COURSES

7. ORDER COURSENAME DESC ASC

8. ORDER BY load_date ASC

9. SELECT COUNT(*) AS cuenta FROM cursos;

10. SELECT name AS nombre, date AS fecha FROM cursos;


11. UPDATE CURSOS
SET FIRSTNAME=’Carolina’
WHERE FIRSTNAME=’Julian’

12. DELETE * FROM CURSOS


WHERE CURSEID=1;

13. SELECT * FROM INSTRUCTORS


WHERE INSTRUCTORID IN (2,5,7)

14. SELECT * FROM INSTRUCTORS


WHERE SALARY BETWEEN 5000 AND 7000

15. SELECT * FROM STUDENTS


WHERE LASTNAME LIKE ‘%Z’

16. SELECT NAME, LASTNAME FROM STUDENTS


WHERE AGE=20

17. SELECT FIRSTNAME, LASTNAME FROM STUDENTS


WHERE LASTNAME LIKE ‘%O%’

18. SELECT * FROM INSTRUCTORS


WHERE SALARY > 50000 AND FIRSTNAME LIKE ‘J%’

19. SELECT *FROM STUDENTS


WHERE FIRSTNAME IS NOT NULL

20. SELECT COURSEID, COUNT(STUDENTID)


FROM STUDENT_COURSE
GROUP BY COURSEID
HAVING COUNT (STUDENTID)>1

21. SELECT sum(SALARY) AS TOTALSALARY


FROM INSTRUCTORS
22. SELECT min(AGE) AS MINAGE
FROM STUDENTES
23. SELECT AGE, CASE WHEN AGE BETWEEN 18 AND 20 THEN
‘TEAMA’ ELSE ‘TEAMB’ END
FROM STUDENTS
GROUP BY AGE

24. SELECT courses.id AS 'id', courses.name AS 'name',


courses.teacher_id AS 'teacher_id', teachers.name AS 'teacher_name'
FROM courses
INNER JOIN teachers ON courses.teacher_id=teachers.id;

VISTAS:

1. CREATE OR REPLACE VIEW VW_STUDENTS AS


SELECT STUDENT_ID, FIRSTNAME, LASTNAME
FROM STUDENTS;

CTE (Common Table Expressions):


1. WITH AverageSalaries AS (
SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID=d.DepartmentID
GROUP BY d.DepartmentName
)

SELECT DepartmentName, AvgSalary


FROM AverageSalaries
WHERE AvgSalary>65000;

You might also like