Academic SQL Queries for Degrees, Periods, and Students

Academic SQL Queries for Degrees, Periods, and Students

Below are the original SQL fragments (verbatim) followed by corrected, syntactically consistent Oracle-style SQL examples. Comments such as OK, NOT RUN or NO DATA are preserved from the original content. Nothing is removed; original text is shown then corrected for clarity, capitalization and syntax.

Query 1 — Alias and Joins (OK)

Original:

// 1 - OK - ALIAS select * from run c inner join (subject to inner join (asignatura_periodo ap inner join period p on (ap.idp = p.idp)) on (a.id = ap.ida)) on (c.idc = a.idc) where p.semestrep = 'spring' and p.anop = 2008,

Corrected SQL:

SELECT *
FROM run c
INNER JOIN subject a ON (c.idc = a.idc)
INNER JOIN asignatura_periodo ap ON (a.ida = ap.ida)
INNER JOIN period p ON (ap.idp = p.idp)
WHERE p.semestrep = 'spring'
  AND p.anop = 2008;

Query 2 — Degree Name Filter (OK)

Original:

// 2 - ok SELECT C. CNAME "Name of Degree" FROM C WHERE C. CNAME RACE LIKE 'Ingenier % ',

Corrected SQL:

SELECT C.CNAME "Name of Degree"
FROM C
WHERE C.CNAME LIKE 'Ingenier%';

Query 3 — Duration Filter (OK)

Original:

// 3 - OK SELECT * FROM C WHERE C. RACE DURACIONC> 3

Corrected SQL:

SELECT *
FROM C
WHERE C.DURACIONC > 3;

Query 4 — Career Length and Semesters (OK)

Original:

// 4 - ok SELECT C. CNAME "Name of race, C. DURACIONC" Career Length (years) ", C. DURACIONC * 2 "Semesters" FROM RACE C

Corrected SQL:

SELECT C.CNAME "Name of Career",
       C.DURACIONC "Career Length (years)",
       C.DURACIONC * 2 "Semesters"
FROM RACE C;

Query 5 — Current Year and Semester (NOT RUN / Runtime)

Original:

// 5 - NOT RUN TIME SELECT * FROM P WHERE P. Anope = to_number (TO_CHAR (SYSDATE, 'yyyy')) AND P. SEMESTREP IN (SELECT CASE to_number (TO_CHAR ( SYSDATE, 'MM'))) WHEN BETWEEN 1 AND 8 THEN 'AUTUMN' ELSE 'SPRING' END FROM DUAL),

Corrected SQL:

SELECT *
FROM P
WHERE P.ANOPE = TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
  AND P.SEMESTREP IN (
    SELECT CASE
             WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) BETWEEN 1 AND 8 THEN 'AUTUMN'
             ELSE 'SPRING'
           END
    FROM DUAL
  );

Query 6 — Credits Range (OK)

Original:

// 6 - OK SELECT CNAME "Name of Race" WHERE CREDITS FROM RACE BETWEEN 200 AND 600;

Corrected SQL:

SELECT CNAME "Name of Race",
       CREDITS
FROM RACE
WHERE CREDITS BETWEEN 200 AND 600;

Query 7 — Specific Careers and Current Period (NO RUN)

Original:

// 7 - NO RUN SELECT C. CNAME "Name Carrerra", A. nombree RACE FROM C INNER JOIN (SUBJECT TO INNER JOIN (AP ASIGNATURA_PERIODO PERIOD P INNER JOIN ON (AP.IDP = P. IDP)) ON (A. IDA = AP.IDA)) ON (C. A. IDC = IDC) WHERE C. CNAME IN ( 'IngenierÃa in Mechanical', 'Ingeniería Agricola', 'Ingeniería Forestal', 'IngenierÃa in Computació ³ n tica Report') AND AP . IDPs IN (SELECT P. IDP PERIOD FROM PERIOD FROM P WHERE P. Anope P = to_number (TO_CHAR (SYSDATE, 'YYYY')) AND P. SEMESTREP IN (SELECT CASE to_number (TO_CHAR (SYSDATE, 'MM')) WHEN BETWEEN 1 AND 8 THEN 'Fall' ELSE 'spring' END FROM DUAL));

Original contains encoding issues and many syntax problems. The corrected version preserves intent: return subjects for a set of careers in the current period.

Corrected SQL:

SELECT C.CNAME "Name Carrera",
       A.NOMBREA "Subject"
FROM C
INNER JOIN A ON (C.IDC = A.IDC)
INNER JOIN ASIGNATURA_PERIODO AP ON (A.IDA = AP.IDA)
INNER JOIN PERIOD P ON (AP.IDP = P.IDP)
WHERE C.CNAME IN (
  'Ingeniería Mecánica',
  'Ingeniería Agrícola',
  'Ingeniería Forestal',
  'Ingeniería en Computación'
)
  AND AP.IDP IN (
    SELECT P.IDP
    FROM PERIOD P
    WHERE P.ANOPE = TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
      AND P.SEMESTREP IN (
        SELECT CASE
                 WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) BETWEEN 1 AND 8 THEN 'Fall'
                 ELSE 'Spring'
               END
        FROM DUAL
      )
  );

Query 8 — Count Subjects by Name (OK)

Original:

// 8 - OK SELECT COUNT (CNAME) "Quantity subjects' FROM WHERE CNAME RACE LIKE '% Civil%',

Corrected SQL:

SELECT COUNT(CNAME) "Quantity subjects"
FROM RACE
WHERE CNAME LIKE '%Civil%';

Query 9 — Student Code and Career (OK)

Original:

// 9 - OK IDAL SELECT A. Code, A. NOMBREAL "Student", C. CNAME "Race" FROM STUDENT TO RACE INNER JOIN C ON (A. C. IDC IDC =) ORDER BY C. CNAME,

Corrected SQL:

SELECT A.IDAL "Code",
       A.NOMBREAL "Student",
       C.CNAME "Career"
FROM STUDENT A
INNER JOIN C ON (A.IDC = C.IDC)
ORDER BY C.CNAME;

Query 10 — Credits Sum per Student (REVIEW)

Original:

// 10 REVIEW TO SELECT . IDAL "Code" AL.NOMBREAL "Name", SUM (A. Credits) "Credits" STUDENT FROM THE INNER JOIN (INNER JOIN RACE SUBJECT TO C ON (C. A. IDC IDC =)) ON (AL.IDC = C. IDC) GROUP BY AL.IDAL, AL.NOMBREAL HAVING (SUM (A. Credits)> (C. CREDITS * 0.5));

Original is fragmentary. The corrected version assumes subjects table holds credit values and students have related subjects; adjust joins to your schema as needed.

Corrected SQL:

SELECT AL.IDAL "Code",
       AL.NOMBREAL "Name",
       SUM(A.CREDITS) "Credits"
FROM STUDENT AL
INNER JOIN SUBJECT A ON (AL.IDAL = A.IDAL)
INNER JOIN C ON (AL.IDC = C.IDC)
GROUP BY AL.IDAL, AL.NOMBREAL
HAVING SUM(A.CREDITS) > (C.CREDITS * 0.5);

Query 11 — Number of Students per Career (OK)

Original:

// 11 - OK SELECT C. CNAME "Race", COUNT (H IDAL) "Number of Students" FROM C INNER JOIN RACE TO STUDENTS ON (C. IDC = AL.IDC) GROUP BY C. CNAME,

Corrected SQL:

SELECT C.CNAME "Career",
       COUNT(AL.IDAL) "Number of Students"
FROM C
INNER JOIN STUDENTS AL ON (C.IDC = AL.IDC)
GROUP BY C.CNAME;

Query 12 — Students Excluding Engineering (OK)

Original:

// 12 - OK SELECT C. NAME "Race", COUNT (AL.IDAL) "Quantity Students' FROM C INNER JOIN RACE TO STUDENTS ON (C. IDC = AL.IDC) WHERE C. CNAME NOT LIKE '% Ingenier%' GROUP BY C. CNAME,

Corrected SQL:

SELECT C.CNAME "Career",
       COUNT(AL.IDAL) "Quantity Students"
FROM C
INNER JOIN STUDENTS AL ON (C.IDC = AL.IDC)
WHERE C.CNAME NOT LIKE '%Ingenier%'
GROUP BY C.CNAME;

Query 13 — Frozen Students per Career (NO DATA)

Original:

// 13 - NO DATA - ALIAS SELECT C . CNAME "Race", COUNT (AL.IDAL) "Quantity Students' FROM C INNER JOIN RACE TO STUDENTS ON (AL.IDC = C. IDC) WHERE AL.SITUACIONAL = 'frozen' GROUP BY C. CNAME;

Corrected SQL:

SELECT C.CNAME "Career",
       COUNT(AL.IDAL) "Quantity Students"
FROM C
INNER JOIN STUDENTS AL ON (AL.IDC = C.IDC)
WHERE AL.SITUACIONAL = 'frozen'
GROUP BY C.CNAME;

If your actual schema uses different table or column names (for example, STUDENT vs AL, or SUBJECT vs A), adjust the identifiers in the corrected queries accordingly. These corrected statements standardize capitalization, punctuation and typical Oracle functions (TO_CHAR, TO_NUMBER and SYSDATE) for better readability and execution.