본문 바로가기

Programming/SQL7

BigQuery code for Alex the Analyst SQL portfolio project Hi guys I've created a bigquery code for bigquery for those who doesn't use the sql server that alex uses in his video. Let me know if there are any issues! Please like and comment below! :) -- joins, ctes, temp tables, windows function, aggregate function, creating views, converting data types SELECT * FROM `onyx-smoke-380601.portfolio_1.CovidDeaths` where continent is not null order by 3,4 LIM.. 2023. 5. 28.
[SQL 정리] DDL -- DDL --1) 테이블 생성 create table IF NOT EXISTS dept_2 ( deptno INT, dname varchar(10), loc varchar(10)); -- 2) default create table IF NOT EXISTS dept_3 ( deptno int, dname varchar(10), loc varchar(10) default '서울'); insert into dept_3(deptno,dname) values ( 1, '개발'); select * from dept_3; -- 3) 제약조건 -- 제약조건 확인 SELECT * FROM information_schema.table_constraints WHERE TABLE_NAME = 'DEPT'; -- 가. pr.. 2023. 5. 28.
[SQL 정리] DML -- 1. insert 문 -- 1) 단일 생성 insert into dept(deptno, dname, loc ) values ( 50, '개발', '서울'); insert into dept(deptno, dname ) values ( 60, '개발'); -- 2) 멀티 생성 create table copy_dept as select * from dept; SELECT * FROM copy_dept; create table copy_dept2 as select * from dept where 1=2; SELECT * FROM copy_dept2; -- 2. update 문 update dept set dname ='인사', loc='제주' where deptno = 50; -- 3. delete 문 d.. 2023. 5. 25.
[SQL 정리] 서브쿼리 -- 1) where 절 서브쿼리 select empno, ename, job, sal from emp where sal > ( select sal from emp where ename ='WARD'); -- 2) 그룹함수 이용 select empno, ename, job, sal from emp where sal > ( select avg(sal) from emp ); -- 2. 복수행 서브쿼리 -- 업무별 최소급여를 받는 사원 조회 select empno, ename, job, sal from emp where sal IN ( select MIN(sal) from emp Group by job ); -- 업무가 MANAGER인 사원의 최소급여보다 적은 급여를 받는 사원 조회 select empno, .. 2023. 5. 25.

티스토리툴바