How to fix this error in pgadmin 4 ===================================== ERROR: syntax error at or near ""students"" LINE 121: DROP "students"; ^ SQL state: 42601 Character: 4630 ================================================================================== CREATE TABLE "courses"( "courseId" SERIAL, "courseName" VARCHAR(120) NOT NULL, CONSTRAINT "courses_pk" PRIMARY KEY ("courseId") ); CREATE TABLE "students"( "studentId" SERIAL, "courseId" INT NOT NULL, "studentName" VARCHAR(120) NOT NULL, "studentEmail" VARCHAR(120) UNIQUE NOT NULL, "studentPhone" INT NOT NULL, CONSTRAINT "students_pk" PRIMARY KEY ("studentId"), CONSTRAINT "courses_students_fk" FOREIGN KEY("courseId") REFERENCES "courses"("courseId") ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID ); --insert values into courses table INSERT INTO "courses" ("courseName") VALUES ('Web Development'); INSERT INTO "courses"("courseName") VALUES('Java Development'); -- insert 2 records into the courses table INSERT INTO "courses"("courseName") VALUES('Python Development'),('Ruby Development'); --insert another record into the courses table INSERT INTO "courses"("courseName") VALUES('C++ development'); --select all the records from table select * from "courses"; --select courseName column from courses table select "courseName" from "courses"; --select all the records from students table select * from "students"; -- Get all the courses SELECT "courseId", "courseName" FROM "courses"; -- Get all the students SELECT "studentId", "courseId", "studentName", "studentEmail", "studentPhone" FROM "students"; --insert values into students table INSERT INTO "students" ("courseId", "studentName", "studentEmail", "studentPhone") VALUES (1, 'John Doe', 'john@doe.com', 2716272); --insert another record into students table INSERT INTO "students"("courseId","studentName","studentEmail","studentPhone") VALUES(2, 'John Doe2','john@doe2.com',2716273); --insert 2 records into students table INSERT INTO "students" ("courseId","studentName", "studentEmail","studentPhone") VALUES (3,'John Doe3', 'john@doe3.com',2716274), (4,'John Doe4', 'john@doe4.com',2716275); -- insert another record into students table INSERT INTO "students"("courseId","studentName","studentEmail","studentPhone") VALUES(5, 'John Doe5','john@doe5.com',2716276); select * from "courses"; select * from "students"; delete from "students"; --get the maximum value of the serial column SELECT MAX("studentId") FROM "students"; -- this can reset the value of serial column in the table --SETVAL('sequence_name', new_value, is_called) SELECT SETVAL(pg_get_serial_sequence('students','studentId'),100,false); -- Get all the students who have a courseId = 2 SELECT * FROM "students" WHERE "courseId" = 2; --get all the student who have studentId=3 SELECT * FROM "students" WHERE "studentId"=3; SELECT MAX("studentId") from "students"; -- Get all the students whose studentId is greater than 1 but less than 5 SELECT * FROM "students" WHERE "studentId" > 1 AND "studentId" < 5; -- Get all the students who do not have data stored for studentPhone SELECT * FROM "students" WHERE "studentPhone" IS NULL; -- Get all the students whose studentName starts with Jo no matter what other letters might follow the Jo SELECT * FROM "students" WHERE "studentName" LIKE 'Jo%'; -- Get all the students whose courseId is present in the list (1, 5) SELECT * FROM "students" WHERE "courseId" IN ( 1, 5 ); -- Get all the students whose courseIds are between 1 AND 5 SELECT * FROM "students" WHERE "courseId" BETWEEN 1 AND 5; --Get all the students whose courseIds are greater than 1 and less than 5 SELECT * from "students" where "courseId">1 AND "courseId"<5; -- Get all the students who have a courseId that is either 1 or 2 SELECT * FROM "students" WHERE "courseId" = 1 OR "courseId" = 2; -- Get all the students who have a courseId = 1 and a missing studentPhone SELECT * FROM "students" WHERE "courseId" = 1 AND "studentPhone" IS NULL; -- Get all the students who have a courseId that is either 1 or 2 SELECT * FROM "students" WHERE "courseId" = 1 OR "courseId" = 2; --Update name of the student that has studentId = 1 to 'Alex' UPDATE "students" SET "studentName" = 'Alex' WHERE "studentId" = 1; -- Update studentPhone to 18273124 where studentId = 1 and studentName = 'Alex' UPDATE "students" SET "studentPhone" = 18273124 WHERE "studentId" = 1 AND "studentName" = 'Alex'; -- Update studentPhone to 0 where studentId greater-than 1 and less-than 5 UPDATE "students" SET "studentPhone" = 0 WHERE "studentId" > 1 AND "studentId" < 5; -- Delete a student whose studentId = 1 DELETE FROM "students" WHERE "studentId" = 1; select * from "students"; -- Delete all students whose studentPhone field is empty DELETE FROM "students" WHERE "studentPhone" IS NULL; DELETE FROM "students"; DROP "students"; DELETE FROM "courses"; DROP "courses";
How to fix this error in pgadmin 4
=====================================
ERROR: syntax error at or near ""students"" LINE 121: DROP "students"; ^ SQL state: 42601 Character: 4630
==================================================================================
CREATE TABLE "courses"(
"courseId" SERIAL,
"courseName" VARCHAR(120) NOT NULL,
CONSTRAINT "courses_pk" PRIMARY KEY ("courseId")
);
CREATE TABLE "students"(
"studentId" SERIAL,
"courseId" INT NOT NULL,
"studentName" VARCHAR(120) NOT NULL,
"studentEmail" VARCHAR(120) UNIQUE NOT NULL,
"studentPhone" INT NOT NULL,
CONSTRAINT "students_pk" PRIMARY KEY ("studentId"),
CONSTRAINT "courses_students_fk" FOREIGN KEY("courseId")
REFERENCES "courses"("courseId")
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
);
--insert values into courses table
INSERT INTO "courses" ("courseName") VALUES ('Web Development');
INSERT INTO "courses"("courseName") VALUES('Java Development');
-- insert 2 records into the courses table
INSERT INTO "courses"("courseName") VALUES('Python Development'),('Ruby Development');
--insert another record into the courses table
INSERT INTO "courses"("courseName") VALUES('C++ development');
--select all the records from table
select * from "courses";
--select courseName column from courses table
select "courseName" from "courses";
--select all the records from students table
select * from "students";
-- Get all the courses
SELECT "courseId", "courseName" FROM "courses";
-- Get all the students
SELECT "studentId", "courseId", "studentName", "studentEmail", "studentPhone" FROM "students";
--insert values into students table
INSERT INTO "students" ("courseId", "studentName", "studentEmail", "studentPhone")
VALUES (1, 'John Doe', 'john@doe.com', 2716272);
--insert another record into students table
INSERT INTO "students"("courseId","studentName","studentEmail","studentPhone")
VALUES(2, 'John Doe2','john@doe2.com',2716273);
--insert 2 records into students table
INSERT INTO "students" ("courseId","studentName", "studentEmail","studentPhone")
VALUES (3,'John Doe3', 'john@doe3.com',2716274),
(4,'John Doe4', 'john@doe4.com',2716275);
-- insert another record into students table
INSERT INTO "students"("courseId","studentName","studentEmail","studentPhone")
VALUES(5, 'John Doe5','john@doe5.com',2716276);
select * from "courses";
select * from "students";
delete from "students";
--get the maximum value of the serial column
SELECT MAX("studentId") FROM "students";
-- this can reset the value of serial column in the table
--SETVAL('sequence_name', new_value, is_called)
SELECT SETVAL(pg_get_serial_sequence('students','studentId'),100,false);
-- Get all the students who have a courseId = 2
SELECT * FROM "students" WHERE "courseId" = 2;
--get all the student who have studentId=3
SELECT * FROM "students" WHERE "studentId"=3;
SELECT MAX("studentId") from "students";
-- Get all the students whose studentId is greater than 1 but less than 5
SELECT * FROM "students" WHERE "studentId" > 1 AND "studentId" < 5;
-- Get all the students who do not have data stored for studentPhone
SELECT * FROM "students" WHERE "studentPhone" IS NULL;
-- Get all the students whose studentName starts with Jo no matter what other letters might follow the Jo
SELECT * FROM "students" WHERE "studentName" LIKE 'Jo%';
-- Get all the students whose courseId is present in the list (1, 5)
SELECT * FROM "students" WHERE "courseId" IN ( 1, 5 );
-- Get all the students whose courseIds are between 1 AND 5
SELECT * FROM "students" WHERE "courseId" BETWEEN 1 AND 5;
--Get all the students whose courseIds are greater than 1 and less than 5
SELECT * from "students" where "courseId">1 AND "courseId"<5;
-- Get all the students who have a courseId that is either 1 or 2
SELECT * FROM "students" WHERE "courseId" = 1 OR "courseId" = 2;
-- Get all the students who have a courseId = 1 and a missing studentPhone
SELECT * FROM "students" WHERE "courseId" = 1 AND "studentPhone" IS NULL;
-- Get all the students who have a courseId that is either 1 or 2
SELECT * FROM "students" WHERE "courseId" = 1 OR "courseId" = 2;
--Update name of the student that has studentId = 1 to 'Alex'
UPDATE "students" SET "studentName" = 'Alex' WHERE "studentId" = 1;
-- Update studentPhone to 18273124 where studentId = 1 and studentName = 'Alex'
UPDATE "students" SET "studentPhone" = 18273124 WHERE "studentId" = 1 AND "studentName" = 'Alex';
-- Update studentPhone to 0 where studentId greater-than 1 and less-than 5
UPDATE "students" SET "studentPhone" = 0 WHERE "studentId" > 1 AND "studentId" < 5;
-- Delete a student whose studentId = 1
DELETE FROM "students" WHERE "studentId" = 1;
select * from "students";
-- Delete all students whose studentPhone field is empty
DELETE FROM "students" WHERE "studentPhone" IS NULL;
DELETE FROM "students";
DROP "students";
DELETE FROM "courses";
DROP "courses";
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
