Posts

Creating a dns hosted zone in route 53 in aws

 Creating a dns hosted zone in route 53 in aws In this task, we create a dns hosted zone using Amazon Web Services. this helps us manage domain names and connect them to resources like servers or websites. First, go to the aws console and open the route 53 service. route 53 is used for domain name system (dns) management. Next, click on hosted zones and then click create hosted zone. enter your domain name (example: mywebsite.com). choose type as public hosted zone so it can be accessed from the internet. then click create. After creating the hosted zone, aws will automatically generate name servers (ns records). these name servers must be added to your domain registrar (like godaddy) so that your domain points to aws. Now, you can create records inside the hosted zone. for example, you can create an  record to connect your domain to an ec2 public ip address. Once everything is set, it may take some time for dns to update. after that, your domain will start working and point t...
Post a Comment
Read more

Creating a simple ec2 instance and running a web server

  Creating a simple ec2 instance and running a web server In this task, we create a virtual server using Amazon Web Services and run a simple web server. Then we access it from the internet using its public IP address. First, go to the AWS console and open the EC2 service. Click launch instance and choose an operating system like amazon linux. Select the instance type t2.micro. Create or select a key pair and configure the security group to allow ssh (port 22) and http (port 80). After that, launch the instance. Next, connect to the instance using SSH from the terminal with the .pem key file. After logging in, update the system and install the apache web server. Apache is used to host web pages on the server. Once installed, start the apache service so the server begins running. Finally, copy the public IP address of the EC2 instance and open it in a web browser. When the IP address is entered, the default apache web page will appear. This confirms that the web server is runn...
Post a Comment
Read more

Select Queries from DVD Rental database

  Select Queries from DVD Rental database 1. Retrieve film titles and rental rates with aliases select title as "Movie Title" , rental_rate as "Rate" from film; 2. List customer names and email with aliases select first_name as "First Name" , last_name as "Last Name" , email from customer; 3. Films sorted by rental rate (desc), then title (asc) select title, rental_rate from film order by rental_rate desc , title asc ; 4. Actor names sorted by last name, then first name select first_name, last_name from actor order by last_name, first_name; 5. Unique replacement costs select distinct replacement_cost from film; 6. Film title and length with alias select title, length as "Duration (min)" from film; 7. Customer names with active status select first_name, last_name, active as "Is Active" from customer; 8. Film categories sorted alphabetically select name from category order by name; 9....
Post a Comment
Read more

Basic Select SQL Queries

Image
  Basic Select SQL Queries The select statement is used to get data from a table. We can either select all columns using * or choose only the columns we need. The where clause is used to filter rows based on conditions such as id, country, or population. This helps in getting only the required data. Sometimes the result may contain repeated values, so distinct is used to remove duplicates. For searching text based on patterns, like can be used. Aggregate functions such as count help perform calculations on the data.
Post a Comment
Read more

Filter Assignments

  Filter Assignments: 1. Get movies with rental rate greater than 3 SELECT title, rental_rate FROM film WHERE rental_rate > 3; 2. Rental rate > 3 and replacement cost < 20 SELECT title, rental_rate, replacement_cost FROM film WHERE rental_rate > 3 AND replacement_cost < 20; 3. Rated PG or rental rate 0.99 SELECT title, rating, rental_rate FROM film WHERE rating = 'PG' OR rental_rate = 0.99; 4. Top 10 movies by rental rate SELECT title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10; 5. Skip 5 and get next 3 SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3; 6. Skip 5 and get next 3 SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 FETCH NEXT 3 ROWS ONLY; 7. Rental duration between 3 and 7 SELECT title, rental_duration FROM film WHERE rental_duration BETWEEN 3 AND 7; 8. Title starts with A and ends with e SELECT title FROM film WHERE title LIKE 'A%e'; 9. Customers without email SELECT first_name, la...
Post a Comment
Read more

CREATE TABLES

 CREATE TABLES: 1. Students table with unique id create table students (id serial primary key, name text, age int); Ensuring primary key that each student is uniquely identified. 2. Employees table where name and email cannot be NULL create table employees (id serial primary key, name text not null, email text not null, phone_number text); Here,not null ensures that it cannot be empty(for name and email) 3. Users table where username and email are unique create table users (id serial primary key, username text unique, email text unique); Usernames and emails will be unique and duplicates are avoided. 4. Products table with price > 0 and stock >= 0 create table products (id serial primary key, name text, price numeric check (price > 0), stock int check (stock >= 0)); Chech ensures that valid price and non-negative stock. 5. Orders table with default values create table orders (id serial primary key, status text default 'pending', created_at timestamp default current...
Post a Comment
Read more

ALTER TABLES

 ALTER TABLES: 1. Make email NOT NULL in customers ALTER TABLE customers ALTER COLUMN email SET NOT NULL; Ensures that all the future records must have an email. 2. Make username unique in users ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username); Prevents duplicate usernames and it is unique. 3. Add CHECK on price in products ALTER TABLE products ADD CONSTRAINT check_price CHECK (price > 0); Ensures price is always greater than 0 using alter command. 4. Set default status in orders ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending'; Automatically assigns pending if no value is given during insertion. 5. Add salary column with constraints ALTER TABLE employees ADD COLUMN salary INT NOT NULL CHECK (salary > 10000); Ensures salary is mandatory and above 10,000 and it cannot be NULL. 6. Add ON DELETE CASCADE to foreign key ALTER TABLE employees DROP CONSTRAINT employees_department_id_fkey; ALTER TABLE employees ADD CONSTRAINT employees_department_id_...
Post a Comment
Read more