soql query exercise

@isTest
private class SOQLExamples {
    //Return Contact (name, email) with (Account (name, type) with (Owner (name, Profile)).  Limit of 1
    @isTest
    private static void num1() {
        Profile p = [SELECT Id, Name FROM Profile WHERE Name='Standard User']; 
        User userObject = new User(Alias='SysAdmin', 
                                    ProfileId=p.Id, 
                                    Username='[email protected]',
                                    LastName='Hutchins', 
                                    Email='[email protected]',
                                    CommunityNickname='Jorb', 
                                    TimeZoneSidKey = 'GMT',
                                    LanguageLocaleKey = 'en_US',
                                    EmailEncodingKey = 'UTF-8',
                                    LocaleSidKey = 'en_US');
        insert userObject;
        Account actObject = new Account(Name='Johnson Motorsports', Type='New Client', OwnerId=userObject.Id);
        insert actObject;
        Contact ctObject = new Contact(LastName='Nicole', AccountId=actObject.Id, Email='[email protected]');
        insert ctObject;

        Test.startTest();
            Contact result  = [SELECT Name, Email, Account.Name, Account.type, Account.owner.name, Account.owner.profile.name
                               FROM Contact 
                               LIMIT 1];
        Test.stopTest();

        System.assertEquals('Standard User', result.Account.Owner.Profile.Name);
    }

    //return Account (name, type, owner(name,type) ) with all of it's related `Contacts` (name, email)
    @isTest
    private static void num2(){
        //create acccount, user, 
        Profile p = [SELECT Id, Name FROM Profile WHERE Name='Standard User']; 
        User userObject = new User(Alias='Tech', 
                                    ProfileId=p.Id, 
                                    Username='[email protected]',
                                    LastName='Borne', 
                                    Email='[email protected]',
                                    CommunityNickname='BOURNE1', 
                                    TimeZoneSidKey = 'GMT',
                                    LanguageLocaleKey = 'en_US',
                                    EmailEncodingKey = 'UTF-8',
                                    LocaleSidKey = 'en_US');
        insert userObject;
        Account mainAccount = new Account(Name='ACME Account', OwnerId=userObject.Id);
        insert mainAccount;
        List<Contact> contactsForMainAccount = new List<Contact>(); 
        for(Integer i=0;i<10;i++){
            Contact newContact = new Contact(LastName='John ' + i, accountId=mainAccount.id, Email='Hutchins' + i + '@gmail.com');
            contactsForMainAccount.add(newContact);
        }
        insert contactsForMainAccount;

        Test.startTest();
        Account res = [
            SELECT Name, Type, Owner.Name, Owner.Id, (SELECT LastName, Email FROM Contacts)
            FROM Account
            ];
        Test.stopTest();
        System.assertEquals(10, res.Contacts.size());
    }

    @isTest
    private static void num3(){
        //return all `Account`` which have been created in the last 3 months
        List<Account> createdAccountsList = new List<Account>();
        for(Integer i=0;i<4;i++){
            Account act = new Account(Name='John' + i);
            system.debug(act);
            createdAccountsList.add(act);
        }
            // Datetime yesterday = Datetime.now().addDays(-99);
            // Account olderThan90Days = new Account(Name='Older than');
            // Test.setCreatedDate(olderThan90Days.Id, yesterday);
            // createdAccountsList.add(olderThan90Days);
            
            insert createdAccountsList;
        
        Test.startTest();
        List<Account> recentlyCreatedAccounts = [
            SELECT Id, Name, CreatedDate
            FROM Account
            //WHERE CreatedDate = LAST_N_DAYS:90
        ];
        Test.stopTest();
        System.assertEquals(4, recentlyCreatedAccounts.size());
    }
    
    //return all `Account`` where Type is 'Industry' or 'Medical' or 'Tech'. Order by Type desc
    @isTest
    private static void num4(){
        List<Account> allAccounts = new List<Account>();
        for(Integer k=0;k<2;k++){
            Account industryAct = new Account(Name='Test', Type='Industry');
            allAccounts.add(industryAct);
        }
        for(Integer j=0;j<2;j++){
            Account techAccount = new Account(Name='John-Tech', Type='Tech');
            allAccounts.add(techAccount);
        }
        for(Integer y=0;y<2;y++){
            Account medicalAccount = new Account(Name='Medi', Type='Medical');
            allAccounts.add(medicalAccount);
        }
        for(Integer u=0;u<4;u++){
            Account randomAccount = new Account(Name='TotallyRandom', Type='Not the droid youre looking for');
            allAccounts.add(randomAccount);
        }
        insert allAccounts;


        Test.startTest();
        List<Account> accountsWithIndustries = [
            SELECT Name, Type
            FROM Account
            WHERE Type like 'Industry' or Type like 'Medical' or Type like 'Tech'
            ORDER BY Type DESC
        ];

        Test.stopTest();
        System.assertEquals(6, accountsWithIndustries.size());
    }

    @isTest
    private static void num5(){
        List<Account> allAccounts = new List<Account>();
        for(Integer k=0;k<2;k++){
            Account industryAct = new Account(Name='Test', Type='Industry');
            allAccounts.add(industryAct);
        }
        for(Integer j=0;j<2;j++){
            Account techAccount = new Account(Name='John Techy', Type='Tech');
            allAccounts.add(techAccount);
        }
              for(Integer y=0;y<2;y++){
            Account medicalAccount = new Account(Name='Medi John', Type='Medical');
            allAccounts.add(medicalAccount);
        }
        for(Integer u=0;u<4;u++){
            Account randomAccount = new Account(Name='John', Type='Not the droid youre looking for');
            allAccounts.add(randomAccount);
        }
        insert allAccounts;

        Test.startTest();
            List<Account> withNameJohn = [
                SELECT Name, Id
                FROM Account
                WHERE Name like '%john%'
            ];
        Test.stopTest();
        system.assertEquals(8, withNameJohn.size());
    }

    @isTest
    private static void num6(){
        //return all Contact where the parent `Account Annual Revenue` > 50

        Account industryAct = new Account(Name='Test', Type='Industry', AnnualRevenue=49);
        insert industryAct;
        for(Integer g=0;g<3;g++){
            Contact lowAccountRevContact = new Contact(LastName='CONTACT = Low Account reve last name', AccountId=industryAct.Id);
            insert lowAccountRevContact;
        }

        Account techAccount = new Account(Name='John Techy', Type='Tech', AnnualRevenue=3405);
        insert techAccount;
        for(Integer j=0;j<7;j++){
            Contact ctObject = new Contact(LastName='contact = high accouint rev - Nicole', AccountId=techAccount.Id, Email='[email protected]');
            insert ctObject;
        }

        Test.startTest();
        List<Contact> contactWithParentAccountHighRevenue = [
            SELECT Name, Id, Account.AnnualRevenue
            FROM Contact
            WHERE Account.AnnualRevenue > 50
        ];
        Test.stopTest();

        System.debug(contactWithParentAccountHighRevenue);

        System.assertEquals(7, contactWithParentAccountHighRevenue.size());
    }

    @isTest
    private static void num7(){
        //return all Contacts where `Account -> Owner -> Profile Name` === `Admin` (change to standard user since i don't have any admin users)
        Profile p = [SELECT Id, Name FROM Profile WHERE Name='Standard User']; 
        Profile profileForSysAdmin = [SELECT Id, Name FROM Profile WHERE Name='System Administrator']; 
        User userObjectNoSysAdmin = new User(Alias='REgular', 
                                    ProfileId=p.Id, 
                                    Username='[email protected]',
                                    LastName='Hutchins', 
                                    Email='[email protected]',
                                    CommunityNickname='hjk', 
                                    TimeZoneSidKey = 'GMT',
                                    LanguageLocaleKey = 'en_US',
                                    EmailEncodingKey = 'UTF-8',
                                    LocaleSidKey = 'en_US');
        insert userObjectNoSysAdmin;
        User userWithSystemAdmin = new User(Alias='SysAdmin', 
                                    ProfileId=profileForSysAdmin.Id, 
                                    Username='[email protected]',
                                    LastName='Hutchins', 
                                    Email='[email protected]',
                                    CommunityNickname='Jorb', 
                                    TimeZoneSidKey = 'GMT',
                                    LanguageLocaleKey = 'en_US',
                                    EmailEncodingKey = 'UTF-8',
                                    LocaleSidKey = 'en_US');
        insert userWithSystemAdmin;
        Account actObjectWithSysAdmin = new Account(Name='Johnson Motorsports', Type='New Client', OwnerId=userWithSystemAdmin.Id);
        insert actObjectWithSysAdmin;

        Account actObjectNoSysAdmin = new Account(Name='Acme Glass', Type='Old Client', OwnerId=userObjectNoSysAdmin.Id);
        insert actObjectNoSysAdmin;

        for(Integer i=0;i<14;i++){
            Contact ctObject = new Contact(LastName='Nicole', AccountId=actObjectWithSysAdmin.Id, Email='[email protected]');
            insert ctObject;
        }
        for(Integer k=0;k<9;k++){
            Contact ctObjectNoSysAdmin = new Contact(LastName='Nicole', AccountId=actObjectNoSysAdmin.Id, Email='[email protected]');
            insert ctObjectNoSysAdmin;
        }

        Test.startTest();
            List<Contact> adminProfileName = [
                SELECT Name, Id
                FROM Contact
                WHERE Account.Owner.Profile.Name = 'System Administrator'
            ];

            List<Contact> allContactsWithAnyProfile = [
                SELECT Name, Id
                FROM Contact
                WHERE Account.Owner.Profile.Name !=null
            ];
        Test.stopTest();
        System.assertEquals(23, allContactsWithAnyProfile.size());
        System.assertEquals(14, adminProfileName.size());
    }

    @isTest
    private static void num8(){
        //return the number of Accounts where Type = 'Industry'
        List<Account> allAccounts = new List<Account>();

        for(Integer k=0;k<3;k++){
            Account newAccount = new Account(Name='indAccount ' + k, Type='Industry');
            allAccounts.add(newAccount);
        }

        for(Integer y=0;y<7;y++){
            Account nonIndAccount = new Account(Name='Non Ind' + y, Type='Not Industry');
            allAccounts.add(nonIndAccount);
        }

        for(Integer q=0;q<6;q++){
            Account nullact = new Account(Name='Ignore', Type=null);
            allAccounts.add(nullact);
        }

        insert allAccounts;

        Test.startTest();
            Integer numAccounts = [
                SELECT count()
                FROM Account
                WHERE Type = 'Industry'
            ];

            Integer totalAccountsNotNull = [
                SELECT count()
                FROM Account
                WHERE Type !=null
            ];
        Test.stopTest();
        System.assertEquals(3, numAccounts);
        System.assertEquals(10, totalAccountsNotNull);

    }

    @isTest
    private static void num9(){
        //return the sum of AnnualRevenue by Account Type

        Test.startTest();
        //allAccounts should return 6
        List<Account> allAccounts = new List<Account>();
          for(Integer k=0;k<3;k++){
            Account newAccount = new Account(Name='indAccount ' + k, Type='Industry', AnnualRevenue=2);
            allAccounts.add(newAccount);
        }
        for(Integer f=0;f<5;f++){
            Account newAccountNonInd = new Account(Name='nonIndAccount ' + f, Type='Non Industry', AnnualRevenue=3411);
            allAccounts.add(newAccountNonInd);
        }
        insert allAccounts;

        AggregateResult[] sumOfAnRev = [
                SELECT SUM(AnnualRevenue), Type
                FROM Account
                GROUP BY Type
        ];
        Test.stopTest();

        Integer num;
        for (AggregateResult ar : sumOfAnRev){
            if(ar.get('Type') === 'Industry'){
                num = (Integer) ar.get('expr0');
            }
        }
        System.assertEquals(6, num);
    }

    @isTest 
    private static void num10(){
        //return the `AccountId`'s which have 3 or more related contacts

        Account industryAct = new Account(Name='Test', Type='Industry', AnnualRevenue=49);
        insert industryAct;
        for(Integer g=0;g<2;g++){
            Contact lowAccountRevContact = new Contact(LastName='CONTACT = Low Account reve last name', AccountId=industryAct.Id);
            insert lowAccountRevContact;
        }

        Account techAccount = new Account(Name='John Techy', Type='Tech', AnnualRevenue=3405);
        insert techAccount;
        for(Integer j=0;j<7;j++){
            Contact ctObject = new Contact(LastName='contact = high accouint rev - Nicole', AccountId=techAccount.Id, Email='[email protected]');
            insert ctObject;
        }

        Test.startTest();
            List<Account> accounts = [
                SELECT Id, Name, (SELECT Name, ID FROM Contacts)
                FROM Account
            ];
            List<Account> updated = new List<Account>();


            for (Account a : accounts){
                if(a.Contacts.size() >= 3){
                    updated.add(a);
                    System.assertEquals(7, a.Contacts.size());
                }         
            }
        Test.stopTest();

        //System.assertEquals(, [SELECT Id, (SELECT Id, Name FROM Contacts) FROM Account]);
    }

    @isTest 
    private static void num11(){
        //return `Account` that have at least one contact where the first name field contains `john`
        Account techAccount = new Account(Name='ADSf Techy', Type='Tech', AnnualRevenue=3405);
        insert techAccount;
        for(Integer j=0;j<7;j++){
            Contact ctObject = new Contact(FirstName='John', LastName='SDSDF', AccountId=techAccount.Id, Email='[email protected]');
            insert ctObject;
        }
        Account randomAccount = new Account(Name='Non', Type='SomeType', AnnualRevenue=341);
        insert randomAccount;
        for(Integer k=0;k<3;k++){
            Contact anotherContact = new Contact(FirstName='Eral', LastName='SLDKJLKAJSF', AccountId=randomAccount.Id, Email='[email protected]');
            insert anotherContact;
        }

        Test.startTest();
            List<Account> accountsWithJohn = [
                SELECT Name, Id
                FROM Account
                WHERE Id IN (SELECT Contact.AccountId FROM Contact WHERE FirstName like '%john%')
                ];
                system.debug(accountsWithJohn);
        Test.stopTest();
        System.assertEquals(1, accountsWithJohn.size());
    }
    @isTest
    private static void num12(){
        //return `Account` which DO NOT have any contacts
        Account techAccount = new Account(Name='ADSf Techy', Type='Tech', AnnualRevenue=3405);
        insert techAccount;
        for(Integer j=0;j<7;j++){
            Contact ctObject = new Contact(FirstName='John', LastName='SDSDF', AccountId=techAccount.Id, Email='[email protected]');
            insert ctObject;
        }
        Account randomAccount = new Account(Name='Non', Type='SomeType', AnnualRevenue=341);
        insert randomAccount;
        for(Integer k=0;k<3;k++){
            Contact anotherContact = new Contact(FirstName='Eral', LastName='SLDKJLKAJSF', AccountId=randomAccount.Id, Email='[email protected]');
            insert anotherContact;
        }
        Account withoutContacts = new Account(Name='HELLO', Type='Anything', AnnualRevenue=12);
        insert withoutContacts;



        Test.startTest();
            List<Account> actsWithNoContacts = [ 
                SELECT Name, Id
                FROM Account
                WHERE Id NOT IN (SELECT accountId FROM Contact)
            ];
            system.debug(actsWithNoContacts);
        Test.stopTest();
        system.assertEquals(1, actsWithNoContacts.size());
    }

    @isTest
    private static void num13(){
        //return  all Account WHERE LastModifiedById != OwnerId
        Profile p = [SELECT Id, Name FROM Profile WHERE Name='Standard User']; 
        User userObject = new User(Alias='Tech', 
                                    ProfileId=p.Id, 
                                    Username='[email protected]',
                                    LastName='Borne', 
                                    Email='[email protected]',
                                    CommunityNickname='BOURNE1', 
                                    TimeZoneSidKey = 'GMT',
                                    LanguageLocaleKey = 'en_US',
                                    EmailEncodingKey = 'UTF-8',
                                    LocaleSidKey = 'en_US');
        insert userObject;
        Account mainAccount = new Account(Name='ACME Account', OwnerId=userObject.Id);
        insert mainAccount;
        List<Contact> contactsForMainAccount = new List<Contact>(); 
        for(Integer i=0;i<10;i++){
            Contact newContact = new Contact(LastName='John ' + i, accountId=mainAccount.id, Email='Hutchins' + i + '@gmail.com');
            contactsForMainAccount.add(newContact);
        }
        insert contactsForMainAccount;
        List<Account> allAccounts = [
            SELECT Id, Name
            FROM Account
        ];

        for(Account a : allAccounts){
            system.debug('last modified id');
            system.debug(a.LastModifiedById);
            system.debug('owner ID: ');
            system.debug(a.OwnerId);
        }


        Test.startTest();
            List<Account> actsWithoutOwner = [
                SELECT Id, Name
                FROM Account
            ];
        Test.stopTest();
    }
}